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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
NG1407
Helper I
Helper I

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

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks for the reply from lbendlin.

 

Hi @NG1407 ,

 

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

vlinhuizhmsft_0-1735549388556.png

 

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:

vlinhuizhmsft_1-1735549582994.png

 

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thanks for the reply from lbendlin.

 

Hi @NG1407 ,

 

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

vlinhuizhmsft_0-1735549388556.png

 

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:

vlinhuizhmsft_1-1735549582994.png

 

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
Super User
Super User

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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