locked
Moving Average of a Ratio Measure in DAX RRS feed

  • Question

  • Usually I can figure things out with DAX if I have enough late nights...but this one has me stumped...and it seems easy.

    My table is as below:

    Date Flag Command Account
    20140401 -1      1,242,404              213
    20140401 0      30,661,903        69,717
    20140402 -1      1,258,405              235
    20140402 0      29,793,906        70,163
    20140403 -1      1,292,929              239
    20140403 0      29,204,737        73,205
    20140404 -1      1,119,171              253
    20140404 0      28,113,396        69,049
    20140405 -1          648,214              147
    20140405 0      11,646,215        38,447
    20140406 -1          561,727              136


    I have a measure that gives me the correct results which is a percentage of commands per account: 

    Measure1=sum([Command])/SUM([Accounts])

    I include this measure into my moving average measure to get the 7 day average of commands per account:

    MA Measure=CALCULATE([Measure1],DATESINPERIOD(DimDate[FullDateAlternateKey], LASTDATE(DimDate[FullDateAlternateKey]),-7,DAY))

    The results of the moving average measure seem to be summing 7 days of the COMMAND column and then summing 7 days of the ACCOUNT column and then dividing for a day. What I want is to divide the COMMAND and ACCOUNT column for each day and then find the average for a 7 day period.

    Hopefully I have explained it well enough....like I said, it seems simple but I am not making any progress.

    Thanks in advanced and let me know!

    Tuesday, October 21, 2014 9:15 PM

Answers

  • Your CALCULATE() is simply manipulating the context that the measure is evaluated in, not the order of evaluation; you're saying take the rolling 7 day period, and then after having returned all data in that context, take the sum of [Command] across all of that, then divide that by the sum of [Accounts].

    What you're looking for is AVERAGEX() which will perform an expression for each row in a table (passed as an argument) and then averages the results.

    MA Measure:=
    AVERAGEX(
        DATESINPERIOD( 
            DimDate[FullDate]
            , LASTDATE( DimDate[FullDate] )
            , -7
            , DAY
        )
        , [Measure1]
    )

    The table is DATESINPERIOD(), which gives us a table of seven dates. For each row in that table, [Measure1] is evaluated, giving us 7 values. Those 7 values are summed and then divided by 7.

    • Marked as answer by NickPPANS Tuesday, October 21, 2014 10:38 PM
    Tuesday, October 21, 2014 10:31 PM

All replies

  • Your CALCULATE() is simply manipulating the context that the measure is evaluated in, not the order of evaluation; you're saying take the rolling 7 day period, and then after having returned all data in that context, take the sum of [Command] across all of that, then divide that by the sum of [Accounts].

    What you're looking for is AVERAGEX() which will perform an expression for each row in a table (passed as an argument) and then averages the results.

    MA Measure:=
    AVERAGEX(
        DATESINPERIOD( 
            DimDate[FullDate]
            , LASTDATE( DimDate[FullDate] )
            , -7
            , DAY
        )
        , [Measure1]
    )

    The table is DATESINPERIOD(), which gives us a table of seven dates. For each row in that table, [Measure1] is evaluated, giving us 7 values. Those 7 values are summed and then divided by 7.

    • Marked as answer by NickPPANS Tuesday, October 21, 2014 10:38 PM
    Tuesday, October 21, 2014 10:31 PM
  • Yes!

    So simple...I knew it would be :)

    Thanks Greg!

    Tuesday, October 21, 2014 10:38 PM