Forum Discussion
Last 12 months average
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 , 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
4 Replies
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
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
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
Thank you very much this seems to work!
