Running total of a measure DAX

Question

I am trying to calculate running total of a measure that I have created, I can get the pivot table to show it using "show Value As" however I cannot achieve the same using a new Measure and I need a new measure so I can then calculate year to date average

using the following formula I get sum of individual rows rather sum of aggregated rows per month

Any help would be appreciated Thanks

       Measure2:=CALCULATE(SUMX(Table,[measure1]),FILTER (
                ALL(  Calendar[Date]),
                Calendar[Date] <= MAX (Calendar[Date] )
            )
        )

Measure1:=if(sum(AMOUNT)=0,Blank(),if(sum(AMOUNT)<0,[WAT],if([Countback]=1,(SUM(AMOUNT)/[CumulativSales1])*[Sum of Days],
                              if([Countback]=2,[Sum of Days]+((SUM(AMOUNT))-[CumulativSales1])/([CumulativSales2]-[CumulativSales1])*[Days Previous],
                              if([Countback]=3,[Sum of Days]+[Days Previous]+((SUM(AMOUNT))-[CumulativSales2])/([CumulativSales3]-[CumulativSales2])*[Days Previous2],
                               if([Countback]=4,[Sum of Days]+[Days Previous]+[Days Previous2]+((SUM(AMOUNT))-[CumulativSales3])/([CumulativSales4]-[CumulativSales3])*[Days Previous3],
                           if([Countback]=5,[Sum of Days]+[Days Previous]+[Days Previous2]+[Days Previous3]+((SUM(AMOUNT))-[CumulativSales4])/([CumulativSales5]-[CumulativSales4])*[Days Previous4],200)))))))  


    AverageSaleWeight2:=if(HASONEVALUE(Calendar[Date]),
    CALCULATE(sum(INVOICE[Days Given * Amount])/sum(INVOICE[Amount GBP]),
    DATEADD(Calendar[Date],-2,MONTH)),BLANK())

    AverageSaleWeight3:=if(HASONEVALUE(Calendar[Date]),
    CALCULATE(sum(INVOICE[Days Given * Amount])/sum(INVOICE[Amount GBP]),
    DATEADD(Calendar[Date],-3,MONTH)),BLANK())

    .....


    Countback:=IF((DIVIDE([CumulativSales1],sum(Aging[OPEN_DOM_AMOUNT]))>=0.9999,1,
                               IF((DIVIDE([CumulativSales2],SUM(Aging[OPEN_DOM_AMOUNT]))>=0.9999,2,
                              IF((DIVIDE([CumulativSales3],SUM(Aging[OPEN_DOM_AMOUNT]))>=0.9999,3,
                              IF((DIVIDE([CumulativSales4],sum(Aging[OPEN_DOM_AMOUNT]))>=0.9999,4,
                              IF((DIVIDE([CumulativSales5],sum(Aging[OPEN_DOM_AMOUNT]))>=0.9999,5,6))))))))))


    CumulativSales1:=CALCULATE(SUM(INVOICE[Amount GBP]),
    DATESINPERIOD(Calendar[Date],
    LASTDATE(Calendar[Date]),-1,MONTH))




    CumulativSales2:=CALCULATE(SUM(INVOICE[Amount GBP]),
    DATESINPERIOD(Calendar[Date],
    LASTDATE(Calendar[Date]),-2,MONTH))





    WAT:=if(sum([AMOUNT])=0,Blank(),IF([Countback]=1,[AverageSaleWeight],IF([Countback]=2,[AverageSaleWeight1],IF([Countback]=3,[AverageSaleWeight2],IF([Countback]=4,[AverageSaleWeight3],IF([Countback]=5,
    [AverageSaleWeight4],IF([Countback]=6,[AverageSaleWeight5],30)))))))


    Days Previous:=CALCULATE(SUM(Calendar[Days]),
    DATESINPERIOD(Calendar[Date],
    LASTDATE(Calendar[Date]),-2,MONTH))-CALCULATE(SUM(Calendar[Days]),
    DATESINPERIOD(Calendar[Date],
    LASTDATE(Calendar[Date]),-1,MONTH))


    Days Previous2:=CALCULATE(SUM(Calendar[Days]),
    DATESINPERIOD(Calendar[Date],
    LASTDATE(Calendar[Date]),-3,MONTH))-CALCULATE(SUM(Calendar[Days]),
    DATESINPERIOD(Calendar[Date],
    LASTDATE(Calendar[Date]),-2,MONTH))

    ....

`

DAX0110 DAX0110 · Accepted Answer · 2017-12-28T14:58:15

Try this revised version and see if you get the desired result:

Measure3 := CALCULATE( 
    SUMX( VALUES(Calendar[Month]), [measure1] )
    , FILTER( 
         ALL(Calendar)
        , Calendar[Date] <= MAX(Calendar[Date])
        && Calendar[Year] = MAX(Calendar[Year])
        )
    )

Latest Edit: added SUMX VALUES

Running total of a measure DAX

1 Answers