Number of absences per day of week

[mb769]

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.

CALCULATE (

    COUNTROWS(Date),

        DATESBETWEEN (

        Date[Date],

        Min(ABSENCE[StartDate]),

        Min(ABSENCE[EndDate])

    )

)

This gives me the following result:

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.

[Greg_Deckler · Accepted 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]

Perfect!

The GENERATE function did the trick.

[johnt75]

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