I need help in writing a DAX query which works out the difference between two dates(feedback receipt date and acknowledgement date) and minus any working days. e.g. if feedback date was the 12th and acknowledgement date was the 19th, the working days to acknowledge will be 5 days, minus Saturday and Sunday.
I’ve modelled the data in a star schema, with a DimDate table having a working days flag and a bank holidays flag.
I’ve tried the following calculated column:
Working Days to Ack =
VAR DTE =
DATEDIFF(FactComplaints[Feedback receipt date],FactComplaints[Acknowledgment Date],DAY)
RETURN
CALCULATE(
DTE,
DimDate[BankHolidayFlag] = 0,
DimDate[WorkingDayFlag] = 1
)
What I was expecting was for the query to minus the logic in the calculate function, however the column just finds the difference between the two columns, see below:
If this was correct, then the second row would show 31 days and not 45 days if you take into consideration working days.
Try this DAX measure:
Working Days to Ack =
VAR StartDate = FactComplaints[Feedback receipt date]
VAR EndDate = FactComplaints[Acknowledgment Date]
RETURN
CALCULATE(
COUNTROWS(DimDate),
DimDate[Date] >= StartDate,
DimDate[Date] <= EndDate,
DimDate[BankHolidayFlag] = 0,
DimDate[WorkingDayFlag] = 1
)