locked
Create A Rolling Measure Of Another Measure RRS feed

  • Question

  • Hello,

    I will try to explain in a logic and concise way what I need to achieve and where I’m stuck.

    -          Step 1: I have weekly actuals and forecast

    -          Step 2: In the 5th column of the image below I’m creating the ABS ERROR SMOOTHED OVER 2 WEEKS.

    In Excel, the formula of cell E3 would be: =ABS(AVERAGE(B2:B3)-AVERAGE(C2:C3))

    In DAX the formula I’m using is:

    ABS ERROR SMOOTHED (2WK SMOOTHING) :=
    ABS (
        AVERAGEX (
            DATESINPERIOD ( dCalendar[Date], LASTDATE ( dCalendar[Date] ), -2 * 7, DAY ),
            [FORECAST]
        )
            - AVERAGEX (
                DATESINPERIOD ( dCalendar[Date], LASTDATE ( dCalendar[Date] ), -2 * 7, DAY ),
                [ACTUALS]
            )
    )

    -          Step 3: This is where I'm stuck. In the final step, I need to create a running total for the past 4 weeks of the formula in step 2.

    In Excel, the formula of cell F6 would be: =SUM(E3:E6)

    However, in DAX, I can’t seem to be able to come to the desired result no matter what I try. I would truly appreciate your help immensely.

    I also want to mention that if I want to create in DAX a 4WEEK ROLLING of the ABS ERROR, my DAX formula does the job (see formula below), however it does not work for the ABSOLUTE ERROR SMOOTHED measure.

    ABS ERROR 4WKS ROLLING :=
    SUMX (
        DATESINPERIOD (
            dCalendar[Date],
            LASTDATE ( dCalendar[Date] ),
            - 4* 7,
            DAY
        ),
        [ABS ERROR (NO SMOOTHING)]
    )








    Sunday, October 16, 2016 2:33 PM

Answers

  • I believe I have solved it, however, I would kindly asked anyone interested in this exercise and to help me, to give a look to my solutions and provide any suggestions if needed.

    ABS ERROR SMOOTHED (2 wks smoothing) ROLLING (4wks rolling):=SUMX (
        DATESINPERIOD ( dCalendar[Date], LASTDATE ( dCalendar[Date] )-4 * 7DAY ),
        ABS (
            AVERAGEX (
                DATESINPERIOD ( dCalendar[Date], LASTDATE ( dCalendar[Date] )-2 * 7DAY ),
                [FC 4W LAG]
            )
                - AVERAGEX (
                    DATESINPERIOD ( dCalendar[Date], LASTDATE ( dCalendar[Date] )-2 * 7DAY ),
                    [ACTUALS]
                )
        )
            / COUNTROWS (
                DATESINPERIOD ( dCalendar[Date], LASTDATE ( dCalendar[Date] )-1 * 7DAY )
            )
    )

    So the part that was missing from my original attempt was dividing the ABSOLUTE DIFFERENCE by COUNTROWS.

    Monday, October 17, 2016 2:52 PM

All replies

  • Are you sure that you have the logic for this calculation correct? What you are trying to do looks like it is taking the average of an average which is not mathematically correct.
    (see http://geekswithblogs.net/darrengosbell/archive/2014/07/28/the-perils-of-calculating-an-average-of-averages.aspx)

    Couldn't you just copy your 2 week calculation from step 2 and replace the 2's with 4's?


    http://darren.gosbell.com - please mark correct answers

    Sunday, October 16, 2016 11:34 PM
  • Hi Darren,

    a) Couldn't you just copy your 2 week calculation from step 2 and replace the 2's with 4's?

    Unfortunately I can' just use my second step formula and replace the 2's with 4's, because that would smooth the Abs Error over 4 weeks. What I need is to smooth is over just 2 weeks (the current and the previous).

    And after that, I need to aggregate the past 4 weeks of the results I get from the previous step (i.e. a 4 week Rolling Total of the 2 Week Smoothed Abs Error).

    b) Are you sure that you have the logic for this calculation correct?

    No, I am not sure! I would appreciate any help amending my second step formula if that's wrong,  and creating the final one that I need.

    Monday, October 17, 2016 6:53 AM
  • Hi Bond. James Bond,

    I am trying to reproduce your scenario in my local computer and get results without any issue.

    I use the following two tables, and create relationship between them.

      



    First, I create the absolute value of difference between “Forecast” and “Actuals” using below measures:

    FORECAST1:=CALCULATE(SUM(Table6[Forecast]),ALLEXCEPT(Table6,Table6[Week]))  
    ACTUAL1:=CALCULATE(SUM(Table6[Actuals]),ALLEXCEPT(Table6,Table6[Week]))

    ABS ERROR 2WKS SMOOTHED (2WK SMOOTHING):=ABS(
    AVERAGEX(DATESINPERIOD(Table7[date],MAX(Table7[date]),-2*7,DAY),Table6[FORECAST1])
    -AVERAGEX(DATESINPERIOD(Table7[date],MAX(Table7[date]),-2*7,DAY),Table6[ACTUAL1])
    )

    Then calculate the sum of difference last 4 weeks using below measure:

    ABS ERROR 4WKS ROLLING:=SUMX (
        DATESINPERIOD (
            Table7[date],
            LASTDATE (Table7[date]),
            - 4* 7,
            DAY
        ),
        [ABS ERROR 2WKS SMOOTHED (2WK SMOOTHING)]
    )

    Finally, I create a pivot table as the following screenshot.
     
    If this is not what you want, please share more details and sample data snapshot for further analysis.

    Best Regards,
    Angelia


    Monday, October 17, 2016 9:38 AM
  • Hi Angelia,

    Thank you very much for trying to help me.

    I can’t use the measures “FORECAST1” and “ACTUALS1” exactly like you have them, because my “Table6” contains many products and many customers, and I need to slice and dice in the Pivot Table, so I can’t use the ALLEXCEPT part, but I don’t think it should be an issue.

    I simply use:

    FORECAST1:=SUM(Table6[Forecast])
    ACTUALS1:=SUM(Table6[Actuals])


    Moving on, your measure “ABS ERROR 2WKS SMOOTHED (2WK SMOOTHING)” works just like my one did, the difference being that I was using LASTDATE(Table7[date])instead of MAX(Table7[date]), but this also calculates correctly the absolute difference of last 2 weeks average of forecast and actuals.

    The problem is with the last measure, “ABS ERROR 4WKS ROLLING”. It simply does Not add the last 4 weeks of the previous measure, “ABS ERROR 2WKS SMOOTHED (2WK SMOOTHING)”.

    ABS ERROR 4WKS ROLLING:=SUMX (
         DATESINPERIOD (
             Table7[date],
             LASTDATE (Table7[date]),
             - 4* 7,
             DAY
         ),
         [ABS ERROR 2WKS SMOOTHED (2WK SMOOTHING)] -> this measure itself  is too complex and also uses “DATESINPERIOD”. If this was a simple calculation, then the measure would work!
     )

    The attached is a snapshot of the pivot table that I obtain, from my real data, and on row 1 I've highlighted in RED the measure which doesn't work, and in yellow the results that I need from it.


    Monday, October 17, 2016 11:30 AM
  • I believe I have solved it, however, I would kindly asked anyone interested in this exercise and to help me, to give a look to my solutions and provide any suggestions if needed.

    ABS ERROR SMOOTHED (2 wks smoothing) ROLLING (4wks rolling):=SUMX (
        DATESINPERIOD ( dCalendar[Date], LASTDATE ( dCalendar[Date] )-4 * 7DAY ),
        ABS (
            AVERAGEX (
                DATESINPERIOD ( dCalendar[Date], LASTDATE ( dCalendar[Date] )-2 * 7DAY ),
                [FC 4W LAG]
            )
                - AVERAGEX (
                    DATESINPERIOD ( dCalendar[Date], LASTDATE ( dCalendar[Date] )-2 * 7DAY ),
                    [ACTUALS]
                )
        )
            / COUNTROWS (
                DATESINPERIOD ( dCalendar[Date], LASTDATE ( dCalendar[Date] )-1 * 7DAY )
            )
    )

    So the part that was missing from my original attempt was dividing the ABSOLUTE DIFFERENCE by COUNTROWS.

    Monday, October 17, 2016 2:52 PM
  • Hi Bond.James Bond,

    I am very glad to hear that your issue got solved. Please mark corresponding reply which will help find the solution easily.


    Best Regards,
    Angelia Zhang
    Thursday, October 20, 2016 3:26 AM