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 EOMONT...
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

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: