none
DAX Running sum - analyse for a dimension other than time

    Question

  • Hello,

    I've created a rather simple measure of running sum for previous 3 months.

    Prev3MonthAmt:=CALCULATE(FACT[Amount],DATESBETWEEN(allDates[DateKey],FIRSTDATE(DATEADD(allDates[DateKey],-2,month)),ENDOFMONTH(allDates[DateKey])))

    It works fine and shows the running sum per each month.

    The problem begins when I add another field into pivot

    For instance - here is my FACT
    Customer  Month        Score        Amt
    ABC          2013-06     2              100
    ABC          2013-07     2              200
    ABC          2013-08     3              150

    When I filter on '2013-08' and remove Score from report (PIVOT) - the calculated measure looks fine

    Customer Month           Amt      Prev3MonthAmt
    ABC        2013-08        150       450

    But when I add Score it looks like this:
    Customer         Month           Score            Amt                   Prev3MonthAmt
    ABC                2013-08           2                                        300
                                               3              150                     150

    Instead of what I want:
    Customer          Month        Score          Amt           Prev3MonthAmt
    ABC                 2013-08      3               150            450

    I would like to see only the Score of THAT month, but with the Prev3Month calculation disregarding all previous customer's Scores
    Thanks!
    Michael


    Michael


    • Edited by M. Shparber Thursday, October 31, 2013 9:57 AM design
    Thursday, October 31, 2013 9:54 AM

Answers

  • you may use a calculated measure to display the last valid score for the selected time-period:

    LastScore:=CALCULATE(VALUES(Fact[Score]), LASTDATE(allDates[DateKey]))

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, October 31, 2013 10:13 AM

All replies