Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a table ABSENCE with employee absences in this form:
ID | StartDate | EndDate |
1 | 01/09/2022 | 30/09/2022 |
2 | 05/09/2022 | 06/09/2022 |
I also have a DATE Table with all dates, which includes the day of the week (Monday to Sunday)
I am aiming to make a bar chart which shows me the number of absence-days for each day of the week, i.e. something that looks similar to this:
First, I wanted to make a measure which counts all Dates included in an Absence. I have been struggling with codes like this, but it doesn't give me the correct results, since it does not filter on weekday.
Somehow I am missing the clue, but I cannot figure out how and where.
Has anyone encountered a problem like this before?
Many thanks in advance for your help.
Solved! Go to Solution.
@mb769
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/409365
@mb769
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/409365
Perfect!
The GENERATE function did the trick.
You could create a calculated table with all absence dates in it like
Absence dates =
SELECTCOLUMNS(
GENERATE(
'Table',
DATESBETWEEN('Date'[Date], 'Table'[Start date], 'Table'[End date])
),
'Table'[Employee ID],
[Date]
)
link that to your date table and then you can write a simple COUNTROWS measure to show the number of absences over any time period or sliced by day of week etc
User | Count |
---|---|
83 | |
69 | |
68 | |
65 | |
54 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |