Dynamic Rolling Average & Sum based on Slicer Selection

[jovendeluna21]

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!

[amitchandak · Accepted solution]

@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]

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]

@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))

[amitchandak]

@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))

[jovendeluna21]

Still wrong, actually the numbers I have are already correct using my previous DAX measures, it's just that when I use other slicers the numbers do not change, which should be.