Rolling Avg compared to prv period Rolling Average

DAX DAX Calculations
1

Hello
Thanks to @deltaselect I have a rolling average based on a slicer to make the number of months to average dynamic. I want to compare the previous period rolling average to get a variance

image

I’ve tried using the DateAdd as below but that is returning 125.33 when it should be 92. Not sure why this isn’t working …

Av. monthly sales (onboard adjusted) Prv Period =

VAR PeriodstoRoll = [Rolling Months]

RETURN

CALCULATE( [Av.monthly sales (onboard adjusted)],DATEADD(DateT[Date],PeriodstoRoll*-1,MONTH))

I’ve attached a pbix with details on the Requirements page
Rolling avg monthly sales for periods with sales and OnboardDate v3 - Copy.pbix (137.6 KB)

2

Good evening @AliB ,

Hereby a solution, which gives the previous month Average monthly sales (onboard adjusted). When the Rolling Months are 0 or 1 (being not yet or the first month on board), the result is set to 0.

Could you confirm that this is the calculation you are looking for ? (see also attached PBIX, measure and outcome below:

PBIX attached:
Rolling avg monthly sales Onboard incl prev period v4.pbix (136.6 KB)

The measure “Previous Month Average rolling monthly sales (onboard adjusted)” ;

Prev  Month Av rolling monthly sales = if([Rolling Months] <= 1, 0, 
                                    CALCULATE( [Av. monthly sales (onboard adjusted)], DATEADD( DateT[Date], -1, MONTH )))

A possible report including the “Prev month Average rolling monthly sales” below:

image
image977×491 33.4 KB

I hope this answers your question.

Kind regards,
Jan van der Wind

3

Thanks for your help Jan
Its not quite what I’m looking for. I’m trying to compare the dynamic rolling average with the rolling average for the same previous period
eg
if I’m looking at Dec. Rolling Months = 3
Av. monthly sales (onboard adjusted) = Sum(Dec, Nov, Oct)/3
Prv Period Av. monthly sales (onboard adjusted) = Sum( Sept, Aug, July) / 3
NB The divisor is adjusted as previously

Still looking at Dec if Rolling Months = 6
Av. monthly sales (onboard adjusted) = Sum(Dec, Nov, Oct,Sept,Aug,July)/6
Prv Period Av. monthly sales (onboard adjusted) = Sum( June, May, April, Mar, Feb,Jan) / 6
NB The divisor is adjusted as previously

4

Hi @AliB

Please receive hereby an adjustment, which hopefully meets your requirement. Let me know your thoughts.

The measure results to 0, whereby the rolling months period exceeds the starting months (Jan, Feb and Mar are 0, when rolling months periods is 3 ), therefore it needs “[Prev month rolling months]”

PBIX attached:
Rolling avg monthly sales Onboard incl prev period v5.pbix (136.2 KB)

1Prev period  Average rolling monthly sales = 
                            if( [Prev month rolling months] < [Rolling Months Value], 0, 
                                             CALCULATE( [Average monthly sales], DATEADD( DateT[Date], -[Rolling Months], MONTH )))

See the report:

image
image1079×517 36 KB

Kind regards,
Jan van der Wind

1 Like
5

Thank you Jan. I really do appreciate the time you have spent helping me with this