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, etc.

I have attached here the pbi file.

https://drive.google.com/file/d/1XhnWQcAFO1i15tz-Ukna2TjdFL0UKT_8/view?usp=sharing

 

Herewith my formula:

AVE. EXH DAYS (Rolling N Months) =
var _table=
SUMMARIZE(FILTER(ALL('AVE PAYMENT TERMS RAW DATA'),[DATE]<=MAX('AVE PAYMENT TERMS RAW DATA'[DATE])&&[DATE]>EOMONTH(MAX('AVE PAYMENT TERMS RAW DATA'[DATE]),-[Parameter Value])),[DATE],"1", DIVIDE(
SUM ('AVE PAYMENT TERMS RAW DATA'[SUMPROD EXH247A]),SUM ('AVE PAYMENT TERMS RAW DATA'[TOTAL_SPEND])))
var _count=
CALCULATE (
DISTINCTCOUNT ( 'AVE PAYMENT TERMS RAW DATA'[DATE] ),
DATESINPERIOD ( 'AVE PAYMENT TERMS RAW DATA'[DATE],MAX('AVE PAYMENT TERMS RAW DATA'[DATE]), - [Parameter Value], MONTH ),
ALL ( 'AVE PAYMENT TERMS RAW DATA' ))
return DIVIDE(SUMX(_table,[1]),_count)
 
SPEND (Rolling N Months) =
var _table=
SUMMARIZE(FILTER(ALL('AVE PAYMENT TERMS RAW DATA'),[DATE]<=MAX('AVE PAYMENT TERMS RAW DATA'[DATE])&&[DATE]>EOMONTH(MAX('AVE PAYMENT TERMS RAW DATA'[DATE]),-[Parameter Value])),[DATE],"1", CALCULATE(
SUM ( 'AVE PAYMENT TERMS RAW DATA'[TOTAL_SPEND] )))
var _count=
CALCULATE (
DISTINCTCOUNT ( 'AVE PAYMENT TERMS RAW DATA'[DATE] ),
DATESINPERIOD ( 'AVE PAYMENT TERMS RAW DATA'[DATE],MAX('AVE PAYMENT TERMS RAW DATA'[DATE]), - [Parameter Value], MONTH ),
ALL ( 'AVE PAYMENT TERMS RAW DATA' ))
return SUMX(_table,[1])
 
It's not required but is it possible to use two parameters in a measure? Because I want also to have a dynamic last N months. For example, I want to see only last 6 months based on selected date.
 
Thank you!
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))

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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))