Last 12 months average

[yonk]

Hi,

I am trying to calculate average last 12 months debtor days.

The step by step calculation is:

A. average last 3 months sales

B. debtor closing balance

C. days in count

D. debtor days for the month = ( B / (A/C))

E. debtor days last 12 months = average of D for past 12 months.

Now I have got A through D measures working but unable to get a measure working for E.

Because A through D are all measures I couldn't simply use sum(D, datesbetween). Couldn't figure out an alternative.

Thanks for helping

Regards,

Yon

[yonk]

Hi @amitchandak ,

I might not have explained myself clearly so I have screenshot a replicate in excel.

Basically my DAX formula in PowerBI is at the top (highlighted green & working fine) but I'm scratching my head on what the DAX formula needs to be for G(yellow highlighted). In simple terms, G needs to be a rolling 12 months average of F. I don't think the suggested formula gave me that answer.

Thanks for the help

Regards,

yonk

[amitchandak · Accepted solution]

@yonk , I think this type of measure should work

Rolling 12 = calculate(AverageX(Values('Date'[MONTH Year]),[Debtor Days]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

You can check for number of month to only get it when 12 months are there

[yonk]

Thank you very much this seems to work!

[amitchandak]

@yonk , if you need sum till month level and then take Average then

example

Rolling 12 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA