Cumulative sum of average measure

DAX DAX Calculations
1

Hi there,

I have got six years of data. Its regulatory year starts on 1st April and finishes on 31st March. I want to calculate the cumulative average of the sum on a monthly basis for the last 5 years (don’t want to include the current year). I am doing the following steps to calculate:

Calculate the sum of value using following measure:

Sum Normalised Value = CALCULATE(sum(‘Fact table’[30 Mins Normalised Value]), FILTER(‘Date’,‘Date’[Date] <[Current_RY_Yr_Strt] && ‘Date’[Date] >= [last 5 years]))

Average of sum using following measure:

Average Normalized Value = AVERAGEX( VALUES(‘Date’[RY Month Year Sort]),[Sum Normalised Value])

Cumulative sum of average using following measure:

Cumulative Norm Value =

Var current_date = MAX(‘Fact table’[Date] )

RETURN

SUMX(

FILTER(ALL(‘Date’),‘Date’[Date] <= current_date

&& ‘Date’[Date] < [Current_RY_Yr_Strt] ), [Average Normalized Value])

When I ran the report it produces the following output:

image
image841×397 11.5 KB

As you can see that cumulative Norm Value does not cumulative add Average Normalised Value. I am not sure where I made the mistake. Could anyone help me in fixing the issue?

Sample file can be download from here.

2

Hi @leo_89,

Give this a go.

Cumulative Norm Value v2 = 
SUMX(
    FILTER( ALL( 'Date'[RY Month Number], 'Date'[Month Name] ), 
        'Date'[RY Month Number] <= MAX( 'Date'[RY Month Number] ) 
    ),  CALCULATE( [Average Normalized Value], FILTER( ALL( 'Date' ), 'Date'[Date] < [Current_RY_Yr_Strt]  ), VALUES( 'Date'[Month Name] ))
)

I hope this is helpful.

1 Like
3

Hi @leo_89, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!