Forum Discussion

jovendeluna21's avatar
jovendeluna21
Icon for Helper IV rankHelper IV
4 years ago
Solved

Dynamic Rolling Average & Sum based on Slicer Selection

Hello, Any idea  on how a dynamic rolling average and spend be dynamic based on slicers? I just noticed that the numbers do not change when I use different slicers such as World Area, Commodity, et...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    jovendeluna21 , With help from the date table, measures like example measure should help

     

    Rolling 30 = calculate(AverageX(Values('Date'[Date]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

jovendeluna21's avatar
jovendeluna21
Icon for Helper IV rankHelper IV
4 years ago

tried this one but giving me incorrect number

Rolling N = calculate(AverageX(Values('Date'[Date]),CALCULATE(sum('AVE PAYMENT TERMS RAW DATA'[TOTAL_SPEND]))),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-[Parameter Value],MONTH))
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

jovendeluna21 , For Days Avg

Rolling N = calculate(AverageX(Values('Date'[Date]),CALCULATE(sum('AVE PAYMENT TERMS RAW DATA'[TOTAL_SPEND]))),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-1* [Parameter Value],MONTH))

 

 

For Months Avg// Sum till month Avg post that


Rolling N = calculate(AverageX(Values('Date'[Month Year]),CALCULATE(sum('AVE PAYMENT TERMS RAW DATA'[TOTAL_SPEND]))),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-1* [Parameter Value],MONTH))