Forum Discussion

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

Dynamic Rolling Monthly Average on Line Chart

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!

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      jovendeluna21
      Icon for Helper IV rankHelper IV
      4 years ago

      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!

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      jovendeluna21
      Icon for Helper IV rankHelper IV
      4 years ago

      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's avatar
        v-yalanwu-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.