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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |