Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to calculate the total working time for a person.
Event: End Shift - Date: 05/08/2019 - Total working time: 4 days
Event: Start Shift - Date: 01/08/2019
If my date slicer is set to be in the range 03/08/2019 to 05/08/2019, I will only see the End Shift Event.
In this case, I want to calcuate the working time from the START of the report period (which is 03/08/2019) to the End Shift event.
So it should show 2 days instead of 4 days.
Hi,
Create a measure as below and drag your date field in the slicer:
Working Days = DATEDIFF( CALCULATE ( MIN(Dates[Date]), ALLSELECTED(Dates) ), CALCULATE ( MAX(Dates[Date]), ALLSELECTED(Dates) ), DAY )
Thanks.
@Anonymous Hi, it doesn't work for me. If there is no data on day 24-08-2015, then the Min date will be the first date with available data.
@Anonymous
So this person is available since 08/22/2019 till 08/25/2019 in the morning when he logged a break.
My date slicer is from 08/23/2019 to 08/25/2019, and i want the working time to be include all the time since start of date slicer till he logged the break.
But since there is no event on day 23 or day 24, the min date was returned as day 25.
Hi @marcus_ushare ,
Does the localdate slicer from another table? please show us your dummy pbix/simple worksheet. we can barely make sure your data structure.
I have created a mock pbix here.
https://drive.google.com/file/d/10CZIw69GMNKt7KXXthGYtvbAomSbu-AE/view?usp=sharing
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |