Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JFarq
Helper I
Helper I

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

Hi

 

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

 

SampleDashboardTable.png

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:

formula2.png

 

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

SourceData.png

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:

PowerBIMatrix1.PNG

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

TablesConnections.PNG

 

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!

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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

View solution in original post

3 REPLIES 3
v-easonf-msft
Community Support
Community Support

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

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:

WorkingAbsenceOccurences1.PNG

speedramps
Super User
Super User

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 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.