Hello,
I am trying to calculate the overall days absent By each week for a month for each employee. I am attaching a sample file here.
I have a custom date table, the employee table, and the absent record table. My calculation for absent days are as follow,
Days Absent in W1 =
CALCULATE(
COUNTROWS(tDates),
DATESBETWEEN(tDates[Dates],
‘tEmployee’[Enter Date],
tEmployee[Leave Date]),
tDates[Week]] = 1,
ALLSELECTED(‘tEmployee’))
I am attaching the file below.
Any ideas or suggestions would be highly suggested. I have tried out a few different ways and none seemed to work.
Count total based on Week.pbix (102.1 KB)
Hi @supergallagher25,
I have changed the data model to fit a star schema following the best modeling practices in Power BI.
You can create the calculated column in the Fact_tEmployees fact table as follows:
Days Absent in W5 =
CALCULATE(
[Total Days Absent],
FILTER(
ALL( Dim_Dates ),
Dim_Dates[Date] >=
[Enter Date] &&
Dim_Dates[Date] <= [Leave Date] &&
Dim_Dates[Week] = 5
)
)
In the example I have used week 5 instead of week 1 to display data.
It is advisable to use a measure instead of a calculated column. To do this, I have created 3 measures:
- Count the records in the Fact_ABS table:
Total Days Absent = COUNTROWS( Fact_ABS )
- Calculate the absent days for each record in the Fact_tEmployees table:
Days Absent in W5 (Measure) =
VAR _EnterDate = SELECTEDVALUE( Fact_tEmployees[Enter Date] )
VAR _LeaveDate = SELECTEDVALUE( Fact_tEmployees[Leave Date] )
VAR _EmpID = SELECTEDVALUE( Fact_tEmployees[EmpID] )
VAR_Result =
CALCULATE(
[Total Days Absent],
FILTER(
ALL( Dim_Dates ),
Dim_Dates[Date] >=
_EnterDate &&
Dim_Dates[Date] <= _LeaveDate &&
Dim_Dates[Week] = 5
),
Dim_Employees[EmpID] = _EmpID
)
RETURN _Result
- Measure to correct the calculation of the totals of the previous measure:
IF(
HASONEVALUE( Fact_tEmployees[EmpID] ),
[Days Absent IN W5 (Measure)],
SUMX(
ADDCOLUMNS(
SUMMARY(
Fact_tEmployees,
[EmpID],
[Enter Date],
[LeaveDate]
),
“Days Absent”, [Days Absent IN W5 (Measure)]
),
[Days Absent IN W5 (Measure)]
)
)
Regards,
Count total based on Week_JAFP.pbix (78.7 KB)
1 Like
It’s great to know that you are making progress with your query @supergallagher25.
Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.
We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,We hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @supergallagher25, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.
