Forum Discussion

JFarq's avatar
JFarq
Icon for Helper I rankHelper I
3 years ago
Solved

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:

 

 

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!

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    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

3 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      JFarq
      Icon for Helper I rankHelper I
      3 years ago

      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: