Forum Discussion
Calculation between Start and End Date
Hi
I have one table named "AbsenceDetailsFact" with columns named date_start, date_end, absence_days. following snap is the data from the table (sample data for one employee). I want to map fact table "AbsenceDetailsFact" with Date dimension table and want to calculate the number of absent employees for a month, year, quarter (user select from slicer). If you look at the row heighlighted, row is showing 4 absence_days because 2 holidays come in between that date range. How to handle this requirement ?
Hi Anonymous ,
Yes, it is possible. I modify my formula to add two variable to get current start,end date for calculation.
Workday = VAR currStart = MIN ( Table[Start] ) VAR currEnd = MAX ( Table[End] ) RETURN COUNTROWS ( FILTER ( CALENDAR ( currStart, currEnd ), WEEKDAY ( [Date], 2 ) < 6 ) )Regards,
Xiapxin Sheng
4 Replies
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365Hi Anonymous ,
Yes, it is possible. I modify my formula to add two variable to get current start,end date for calculation.
Workday = VAR currStart = MIN ( Table[Start] ) VAR currEnd = MAX ( Table[End] ) RETURN COUNTROWS ( FILTER ( CALENDAR ( currStart, currEnd ), WEEKDAY ( [Date], 2 ) < 6 ) )Regards,
Xiapxin Sheng
