Rolling 12 month % average and 5 month % average of each Category

[NG1407]

I am calculating the count of only those ID that has startdate <= 10 and enddate > 10 for each month . In other words need to find count of ID that are active till current selected month. A record is active if its enddate is greater than 10th of that month.

For example for jan month I need to count all those ID that has startdate less than or equal to 10thjan and startdate should contain dates from previous month as well that has end date > 10thjan or in future).

below is the sample data :

Startdate enddate ID
2024-01-10 2024-12-01 a123
2023-12-11 2024-01-11 b123
2024-01-02 2024-11-08 c123
2024-02-11 2024-02-28 d123
2024-03-03 2024-03-10 e123
2024-03-03 2024-03-15 f123
According to above data for jan month the count should be 3, for feb month count should be 0,for march its 1.
Below is the measure I used to calculate count
Num active =
VAR CurrentMonth =
MAX ( 'Date'[Date] )
VAR CutOffDate =
DATE ( YEAR ( CurrentMonth ), MONTH ( CurrentMonth ), 10 )
VAR Result =
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
'Table'[Start date] <= CutOffDate,
'Table'[End date] > CutOffDate
|| ISBLANK ( 'Table'[End date] ),
REMOVEFILTERS ( 'Date' )
)
RETURN
Result

After calculating count of IDs based on start date and end date Now I need further help , I need to calculate percentage average for latest 12 months and 5 months for each category and display in columns as below

Category                 12month % average                               5 month %age average
A
B
C
Example for category A below is the logic
monthlyPercentage = selected month count/totalcount
%age average = sum (monthly percentage of latest 12 months) /12

[v-linhuizh-msft · Accepted solution]

Thanks for the reply from lbendlin.

Hi @NG1407 ,

Using the data you provided, I created the following measures:

ActiveCountpast5months = 
VAR TodayDate =
    TODAY ()
VAR Past10thDates =
    ADDCOLUMNS (
        CALENDAR ( EDATE ( TodayDate, -5 ), TodayDate ),
        "Month10th", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 10 )
    )
RETURN
    SUMX (
        'Table',
        IF (
            COUNTROWS (
                FILTER (
                    Past10thDates,
                    DAY ( [Month10th] ) = 10
                        && [Month10th] >= 'Table'[Startdate]
                        && [Month10th] <= 'Table'[Enddate]
                )
            ) > 0,
            1
        )
    )

%5months average = 
DIVIDE ( [ActiveCountpast5months], COUNTROWS ( ALL ( 'Table' ) ) )

ActiveCountpast12months = 
VAR TodayDate =
    TODAY ()
VAR Past10thDates =
    ADDCOLUMNS (
        CALENDAR ( EDATE ( TodayDate, -12 ), TodayDate ),
        "Month10th", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 10 )
    )
RETURN
    SUMX (
        'Table',
        IF (
            COUNTROWS (
                FILTER (
                    Past10thDates,
                    DAY ( [Month10th] ) = 10
                        && [Month10th] >= 'Table'[Startdate]
                        && [Month10th] <= 'Table'[Enddate]
                )
            ) > 0,
            1
        )
    )

%12months average = 
DIVIDE ( [ActiveCountpast12months], COUNTROWS ( ALL ( 'Table' ) ) )

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.