Month over Month Cumulative Average Help

[PowThomas]

Hello,

I have a line chart that shows a rote average per month via a measure. This is useful. I also want a measure though to show the cumulative average, so that the user can see how each month contributed to the current value. So for instance, in this screenshot, October would still show 20.74 but November would show 19.01 on the chart. How can I accomplish this via DAX?  I'm using fiscal months via a date table. It would also need to keep working in a filter context.

This is what I've tried, but it doesn't work. I'm not sure why.

Cumulative Count =

CALCULATE(

SUMX(

FILTER(

ALL('Dates'[FYMonth]),

'Dates'[FYMonth] <= MAX(Dates[FYMonth]) ), [MyMeasure] ) )

Thanks

[Anonymous]

Hi @PowThomas ， hello Irwan  and FreemanZ , thank you for your prompt reply!

Has the solution from the other users worked for you?

Please let me know if you need any additional support

[FreemanZ]

hi @PowThomas ,

average could be tricky, could you elaborate your requirement with sample data?

[PowThomas]

It will take me a little while to sanitize but I'll try to come back with something. Thank you for looking at it.

[Irwan]

hello @PowThomas 

not sure but it should work. here is a simple example of your DAX

is 'Dates'[FYMonth] in form of month name (October and November) or month number (10 and 11)?

Otherwise, please share your sample data and remove any confidential information.

Hope this will help.

Thank you.

[PowThomas]

Thanks for asking that - I've gotten closer now.

FYMonth are in text forms. The months you see in my example screenshot are from the FYmonth column. When I use the same measure I've been using, but with an FYMonthNum column that is numerical, and then I put the number into the table, it works more or less. The below screenshot is... sort of what I want. The user will want to see the actual month label though - in fact, this will be going into a linechart with the fiscal month name as the x axis. I also don't want the remaining months to populate.

[Irwan]

hello @PowThomas 

yes, looks like you are comparing string in your DAX since FYMonth is in month name so the result might be little bit off.

your screenshot above looks plausible with your DAX. And if your user want to see as month name, you can just add your month name in your table visual then remove the month number.

the month number is only for cumulative calculation since you want to have cumulative result in correct month order (Jan-Feb-Mar) not alphabetic order (Feb-Jan-Mar).

Hope this will help.

Thank you.