Counting how many date ranges have dates that fall within another date range

[JFarq]

Hi

I'm trying to replicate the following excel table in PowerBI:

 

The table displays the number of absent staff per month in the past 24 months.

The table uses the TODAY() and EOMONTH() excel formulae to generate a 'Month', 'End Of Month' and 'Start Of Month' column.

Then using the following formula it counts how many staff were absent (at any point) in each month:

Where Absence_Data2 is my source data of a list of staff with a start and end date for each staff absence period:

I've tried making a DateTable query in PowerBI to generate a 'Month', 'End Of Month' and 'Start Of Month' column in a powerBI matrix:

I've connected my DateTable query to the Absence_Data2 query as follows:

From here I'm not sure how to add an 'Absence Occurences' column to my matrix to count how many staff were absent in each month.

i.e. count how many absence date ranges of my Absence_Data2 query have dates that fall within the date range of each month of my DateTable query.

The source data and powerBI files are available via the follwoing link:

Absence_Occurences_AO_Sample_v5.xlsx

Absence_Dashboard_Sample_v1.pbix

Any help or guidance would be much appreciated!

[v-easonf-msft · Accepted solution]

Hi, @JFarq 

Please try formula like:

calculated column:

Absence Occurences per person per month  =
CALCULATE (
    COUNT ( Absence_Data2[Start Date] ),
    ALLEXCEPT (
        Absence_Data2,
        Absence_Data2[Start Date].[Month],
        Absence_Data2[Start Date].[Year],
        Absence_Data2[Forename And Surname]
    )
)

Measure:

count of absence date =
CALCULATE (
    COUNTROWS ( Absence_Data2 ),
    FILTER (
        Absence_Data2,
        Absence_Data2[Start Date] >= MAX ( 'Calendar (UK?)'[StartOfMonth] )
            && Absence_Data2[Start Date] <= MAX ( 'Calendar (UK?)'[EndOfMonth] )
    )
) + 0

(need change the data type of 'Calendar (UK?)'[StartOfMonth], 'Calendar (UK?)'[EndOfMonth] from 'Text' to 'Date')

Best Regards,
Community Support Team _ Eason

[JFarq]

Thank you very much v-easonf-msft.

Your measure works for me.

I just had to write it out with 'MANUAL End Date' in first line of the second argument of the FILTER() function:

count of absence date = 
CALCULATE (
    COUNTROWS ( Absence_Data2 ),
    FILTER (
        Absence_Data2,
        Absence_Data2[MANUAL End Date] >= MAX ( 'Calendar (UK?)'[StartOfMonth] )
            && Absence_Data2[Start Date] <= MAX ( 'Calendar (UK?)'[EndOfMonth] )
    )
) + 0

To get:

[speedramps]

In Power BI it is best practice to use a calendar table.

All reports use dates so this is really important skill for you to learn

Click here for free training