topic Re: Rolling 12 month % average and 5 month % average of each Category in DAX Commands and Tips

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

NG1407 — Fri, 20 Dec 2024 14:44:50 GMT

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

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

lbendlin — Fri, 20 Dec 2024 16:59:20 GMT

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

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

Anonymous — Mon, 30 Dec 2024 09:08:00 GMT

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.