Forum Discussion

ThomasT's avatar
ThomasT
Frequent Visitor
6 years ago

Running Total past current date

Hi Power BI Users,

Below is a sample table visual, where all attributes are Measures:

 

Actuals = Sum(Actuals)

Burn Rate Avg = Avg of last 6 months

Burn Rate EAC = If Date<Today, Actuals,Burn Rate Ave

Running Burn = Running Total acorss Burn Rate EAC.

 

As you can see the Running Burn goes up to May then remains at the May figure for the remainder on the months. 

 

What I want to achieve is for the Running Burn to cumulative the EAC all the way to Dec.

 

That is Jun = 87.37+20.89 = 108.26; Jul = 108.26+20.89 = 129.15 and so on...

 

Is there a way to achieve this result?

 

Thanks

Need Help

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi ThomasT 

    whats your current statement?

    it should be smth like

    measure = 
var _curDate = MAX(Table[Date])
RETURN
CALCULATE(SUM(Table[EAC]), Table[Date] <= _curDate)
    • ThomasT's avatar
      ThomasT
      Frequent Visitor
      6 years ago

      I gave the DAX formual a try and it is returning total which begins several years in the past.  I want the Jan figure to be starting from the current year and do a running total from that point

      • v-xicai's avatar
        v-xicai
        Community Support
        6 years ago

        Hi ThomasT ,

         

        You may create measure like DAX below.

         

        fins_Running_Burn =
CALCULATE (
    SUM ( Table1[fins_Burn_EAC] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        YEAR ( Table1[Date] ) = YEAR ( MAX ( Table1[Date] ) )
            && MONTH ( Table1[Date] ) <= MONTH ( MAX ( Table1[Date] ) )
    )
)

         

        Best Regards,

        Amy 

         

        Community Support Team _ Amy

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.