Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi I am trying to do some absence calculations. I have a start and end date for each occasion of absence. If I have a date slicer and select a date range, I would like to count the number of days absence within that date range filter, excluding weekends
For example, if someone is absent between 18/09/2017 and 13/10/2017 that is 20 working days (excluding weekends). If I apply a filter between 09/10/2017 and 15/10/2017, I want to count the absence days so for the above example it would show 5 working days. Any idea on the Measure syntax for this?
Thanks
Solved! Go to Solution.
Hi @emmaclarke83,
You can create a calendar table firstly, drag the date from calendar table in slicer, then create a measure below:
Diff =
var start_Date=FIRSTDATE(ALLSELECTED('CALENDAR'[Date]))
var end_Date=LASTDATE(ALLSELECTED('CALENDAR'[Date]))
var current_Start=MAX('Table1'[StartDate])
var current_end=MAX('Table1'[EndDate])
return
IF(current_end>=start_Date&¤t_Start<=end_Date,DATEDIFF(MAX(start_Date,current_Start),MIN(end_Date,current_end),DAY)-COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(MAX(start_Date,current_Start),MIN(end_Date,current_end)),"DoW",WEEKDAY([Date],1)),[DoW]=1||[DoW]=7))+1)
For details, you can see attached pbix file.
Best Regards,
Qiuyun Yu
Hi @emmaclarke83,
You can create a calendar table firstly, drag the date from calendar table in slicer, then create a measure below:
Diff =
var start_Date=FIRSTDATE(ALLSELECTED('CALENDAR'[Date]))
var end_Date=LASTDATE(ALLSELECTED('CALENDAR'[Date]))
var current_Start=MAX('Table1'[StartDate])
var current_end=MAX('Table1'[EndDate])
return
IF(current_end>=start_Date&¤t_Start<=end_Date,DATEDIFF(MAX(start_Date,current_Start),MIN(end_Date,current_end),DAY)-COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(MAX(start_Date,current_Start),MIN(end_Date,current_end)),"DoW",WEEKDAY([Date],1)),[DoW]=1||[DoW]=7))+1)
For details, you can see attached pbix file.
Best Regards,
Qiuyun Yu
Hi Qiuyun Yu
I have just noticed an issue with this measure. It does doesn't SUM. Is there a way round this? I need to be able to sum up total number of days from this measure
Thanks
That's perfect, thank you 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |