Forum Discussion

yonk's avatar
yonk
Regular Visitor
4 years ago
Solved

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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's avatar
yonk
Regular Visitor
4 years ago

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's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      yonk
      Regular Visitor
      4 years ago

      Thank you very much this seems to work!