Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear all,
I've searched in the community, got some good info and I'm close but still can't get it fixed.
I have the following tables:
- Employees table which contains the columns: "EmployeeID", "Start Date" and "End Date"
- Absence table which containes the columsn: "Employee ID", "Start Date" and "End Date"
My ultimate goal is to calculate the absence hours and divide them by the number of employees in a specific weeknumber and show it on a linechart.
The number of employees I use:
NumberOfEmployees= CALCULATE ( DISTINCTCOUNT(Employees[ID]); FILTER( Employees; Employees[Start date] <= LASTDATE ( 'Date'[Date]) && Employees[End date] >= FIRSTDATE ( 'Date'[Date]) || ISBLANK(Employees [End date]) ) )
To get this worked I created a datetable without a relationship.
In this datetable is the weeknumber defined aswell, which I use in the linechart.
Unfortunatly I cant get it done to calculate the absence hours for a given time.
Hope anyone can help me.
Many thanks!
Solved! Go to Solution.
Hi @dax ,
After some real DAX struggles I found a solution, it might be not the perfect DAX, but for now it works.
I had three tables:
1. Employee table 1 - N 2, Absence table
3. Date table
Absence Hours = VAR OutsideWeek = //time between start week ---> end week (+1 to exclude sunday) SUMX ( FILTER ( Absence; Absence[StartDate] <= FIRSTDATE ( 'Date'[Date] ) && Absence[EndDate] >= LASTDATE ( 'Date'[Date])) ; DATEDIFF ( (FIRSTDATE( 'Date'[Date] ) + 1); LASTDATE( 'Date'[Date] ); DAY ) * 8 ) VAR StartIsOutSideWeek = // time between start absence ---> end week SUMX ( FILTER ( Absence; Absence[StartDate] > FIRSTDATE ( 'Date'[Date] ) && Absence[StartDate] < LASTDATE ( 'Date'[Date] ) && Absence[EndDate] >= LASTDATE ( 'Date'[Date] )) ; DATEDIFF ( Absence[StartDate]; LASTDATE ( 'Date'[Date] ); DAY ) * 8 ) VAR EndOutsideWeek = // time between start week ---> end absence (+1 day to exclude sunday) SUMX ( FILTER ( Absence; Absence[StartDate] < FIRSTDATE ( 'Date'[Date] ) && Absence[EndDate] > FIRSTDATE( 'Date'[Date] ) && Absence[EndDate] < LASTDATE ( 'Date'[Date] ) ); DATEDIFF ( (FIRSTDATE('Date'[Date])+1); Absence[EndDate]; DAY ) * 8 ) VAR InsideWeek = // time between start absence ---> end absence SUMX ( FILTER ( Absence; Absence[StartDate] >= FIRSTDATE ( 'Date'[Date] ) && Absence[EndDate] <= LASTDATE ( 'Date'[Date] ) ); DATEDIFF ( Absence[StartDate]; Absence[EndDate]; DAY ) * 8 ) RETURN OutsideWeek + StartIsOutSideWeek + EndOutsideWeek + InsideWeek +[Open Absence Hours]
Open Absence Hours = // calculates the hours for absence with end day blank() VAR StartPreviousWeek = // time between start absence in previous week ---> end week SUMX ( FILTER ( Absence; Absence[StartDate] < LASTDATE ( 'Date'[Date] ) && Absence[StartDate] > FIRSTDATE ( 'Date'[Date] ) && Absence[EndDate] = BLANK () ); DATEDIFF ( Absence[StartDate]; LASTDATE ( 'Date'[Date] ); DAY ) * 8 ) VAR StartOutsideWeek = SUMX ( FILTER ( Absence; Absence[StartDate] <= FIRSTDATE ( 'Date'[Date] ) && Absence[EndDate] = BLANK () ); DATEDIFF ( ( FIRSTDATE ( 'Date'[Date] ) + 1 ); LASTDATE ( 'Date'[Date] ); DAY ) * 8 ) RETURN StartOutsideWeek + StartPreviousWeek
For more details, you can check the file here:
https://www.dropbox.com/s/jte4onfidloiepz/Example.pbix?dl=0
Thanks,
Bwl.
Hi @dax ,
After some real DAX struggles I found a solution, it might be not the perfect DAX, but for now it works.
I had three tables:
1. Employee table 1 - N 2, Absence table
3. Date table
Absence Hours = VAR OutsideWeek = //time between start week ---> end week (+1 to exclude sunday) SUMX ( FILTER ( Absence; Absence[StartDate] <= FIRSTDATE ( 'Date'[Date] ) && Absence[EndDate] >= LASTDATE ( 'Date'[Date])) ; DATEDIFF ( (FIRSTDATE( 'Date'[Date] ) + 1); LASTDATE( 'Date'[Date] ); DAY ) * 8 ) VAR StartIsOutSideWeek = // time between start absence ---> end week SUMX ( FILTER ( Absence; Absence[StartDate] > FIRSTDATE ( 'Date'[Date] ) && Absence[StartDate] < LASTDATE ( 'Date'[Date] ) && Absence[EndDate] >= LASTDATE ( 'Date'[Date] )) ; DATEDIFF ( Absence[StartDate]; LASTDATE ( 'Date'[Date] ); DAY ) * 8 ) VAR EndOutsideWeek = // time between start week ---> end absence (+1 day to exclude sunday) SUMX ( FILTER ( Absence; Absence[StartDate] < FIRSTDATE ( 'Date'[Date] ) && Absence[EndDate] > FIRSTDATE( 'Date'[Date] ) && Absence[EndDate] < LASTDATE ( 'Date'[Date] ) ); DATEDIFF ( (FIRSTDATE('Date'[Date])+1); Absence[EndDate]; DAY ) * 8 ) VAR InsideWeek = // time between start absence ---> end absence SUMX ( FILTER ( Absence; Absence[StartDate] >= FIRSTDATE ( 'Date'[Date] ) && Absence[EndDate] <= LASTDATE ( 'Date'[Date] ) ); DATEDIFF ( Absence[StartDate]; Absence[EndDate]; DAY ) * 8 ) RETURN OutsideWeek + StartIsOutSideWeek + EndOutsideWeek + InsideWeek +[Open Absence Hours]
Open Absence Hours = // calculates the hours for absence with end day blank() VAR StartPreviousWeek = // time between start absence in previous week ---> end week SUMX ( FILTER ( Absence; Absence[StartDate] < LASTDATE ( 'Date'[Date] ) && Absence[StartDate] > FIRSTDATE ( 'Date'[Date] ) && Absence[EndDate] = BLANK () ); DATEDIFF ( Absence[StartDate]; LASTDATE ( 'Date'[Date] ); DAY ) * 8 ) VAR StartOutsideWeek = SUMX ( FILTER ( Absence; Absence[StartDate] <= FIRSTDATE ( 'Date'[Date] ) && Absence[EndDate] = BLANK () ); DATEDIFF ( ( FIRSTDATE ( 'Date'[Date] ) + 1 ); LASTDATE ( 'Date'[Date] ); DAY ) * 8 ) RETURN StartOutsideWeek + StartPreviousWeek
For more details, you can check the file here:
https://www.dropbox.com/s/jte4onfidloiepz/Example.pbix?dl=0
Thanks,
Bwl.
Hi BWL,
I can’t reproduce your design just based on your description, so if possible , could you please inform me more detailed information (such as your sample data and your expecting output)? Then I will help you more correctly.
You could refer to How to Get Your Question Answered Quickly for details.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @dax,
Just created at example pbix file.
Can I share it directly with you, because I don't see where I can upload it in this forum.
KInd regards.
Hi BWL,
You could try to upload pbix file in onedrive or other place which we could access to .
Best Regards,
Zoe Zhi
User | Count |
---|---|
128 | |
112 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
116 | |
102 | |
71 | |
57 |