Dynamic Rolling Monthly Average on Line Chart

[jovendeluna21]

Hi, 

I just want to request any help to have a dynamic rolling monthly average for a line chart. I don't know if there's any problem in the DAX formula I have created because only 1 Month shows correct number. When I try to change the parameter, the number is not correct.

I have uploaded here the copy of the pbix.

https://drive.google.com/file/d/1bVFRkAEcSFnmbleYbfqqwi3zL0Y8FVAY/view?usp=sharing

Thank you!

[v-yalanwu-msft · Accepted solution]

Hi, @jovendeluna21 ;

Please try it.

Rolling N Months2 = 
var _table=
    SUMMARIZE(FILTER(ALL('Raw Data'),[Date]<=MAX('Raw Data'[Date])&&[Date]>EOMONTH(MAX('Raw Data'[Date]),-[Parameter Value])),[Date],"1",  DIVIDE(
        SUM ( 'Raw Data'[SumProd] ) ,SUM ( 'Raw Data'[TOTAL_SPEND] )))
var _count=
    CALCULATE (
        DISTINCTCOUNT ( 'Raw Data'[Date] ),
        DATESINPERIOD ( 'Raw Data'[Date],MAX('Raw Data'[Date]), - [Parameter Value], MONTH ),
        ALL ( 'Raw Data' ))
return DIVIDE(SUMX(_table,[1]),_count)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @jovendeluna21 ;

You could try it.

Rolling N Months = 
DIVIDE (
    CALCULATE (
        SUM ( 'Raw Data'[SumProd] ) / SUM ( 'Raw Data'[TOTAL_SPEND] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), - [Parameter Value], MONTH )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Raw Data'[Date] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), - [Parameter Value], MONTH ),
        ALL ( 'Raw Data' )
    )
)

The final output is shown below:

If not ringht ,can you share the output you want?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jovendeluna21]

Hi, @v-yalanwu-msft , thank you for your response. This is the output I want and I tried in excel. For example, if the dynamic parameter is "3" months rolling average, it should average the last 3 months including the current month such as below. For example "01-Jun-21" rolling N months is the average of April, May and Jun, then ""01-May-21" is the average of Mar, Apr and May. then so on.

[v-yalanwu-msft]

Hi, @jovendeluna21 ;

It may be Rolling N Months2 in my PBIX,

Rolling N Months2 = 
var _table=SUMMARIZE(FILTER(ALL('Raw Data'),[Date]<=MAX('Date'[Date])&&[Date]>=EOMONTH(MAX('Date'[Date]),-[Parameter Value])),[Date],"1",  DIVIDE(
        SUM ( 'Raw Data'[SumProd] ) ,SUM ( 'Raw Data'[TOTAL_SPEND] ))
       )
    var _b=
DIVIDE (
    SUMX(_table,[1]),
    CALCULATE (
        DISTINCTCOUNT ( 'Raw Data'[Date] ),
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), - [Parameter Value], MONTH ),
        ALL ( 'Raw Data' )
    )
)
return IF(MAX([Date])<=MAX('Date'[Date])&&MAX([Date])>=EOMONTH(MAX('Date'[Date]),-[Parameter Value]),_b)

 The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jovendeluna21]

Is it possible that every month has a rolling average? Just like the table below

because when I tried to use the one you created, with 3 as parameter, only the last data point which is 01-Jun-21 has the Rolling N Months2 value

[amitchandak]

@jovendeluna21 , With a date table try like

Rolling N Months =
divide( CALCULATE(
SUM('Raw Data'[SumProd])/SUM('Raw Data'[TOTAL_SPEND]),
DATESINPERIOD('Date'[Date], max('Date'[Date]),-[Parameter Value],MONTH)
)
,
CALCULATE(
DISTINCTCOUNT('Raw Data'[Date]),
DATESINPERIOD('Date'[Date], MAx('Date'[Date]), -[Parameter Value], MONTH)
)
)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[jovendeluna21]

Hi, @amitchandak , thank you for your response! I have tried to do this, however when I try 3 in the parameter, only 1 point is showing which is the latest month only. I'm aiming that every month will have rolling average as well. Is that possible? Thank you!

[amitchandak]

@jovendeluna21 , Please check if the attached file can help

[jovendeluna21]

Hi, @amitchandak , seems like the numbers are erratic.