Forum Discussion

Osmandfernanobi's avatar
Osmandfernanobi
Regular Visitor
6 years ago

Running sum with Period

Dear experts,    I have a situation where I wanted to show the running total in a line chart for Actuals, Expected, and Variance, we are able to get the values and create the visual. But having a s...
DAX
running total
osmandfernando's avatar
osmandfernando
Advocate I
6 years ago

Same Osmand here, I am confused with my community accounts, 

 

ibarrau 

Actual is not a separate table, All those 3 data fields (Actual, Expected, Variance) are in a new table created based on a another table name Burn, 

There are several tables as you can see below

 

 

Thank you

Osmand

ibarrau's avatar
ibarrau
Super User
6 years ago

Well, those measures are probably the "Sum" of something. Can you show us the columns involved in the tables that are used in the measures? Right there I can't see anything related with actual due to measures are not related with the table in which they are created.

 

Regards,

  • osmandfernando's avatar
    osmandfernando
    Advocate I
    6 years ago

    Thank you for the reply

    following are the formulas 

     

     

    Actual = CALCULATE(SUM(Burn[ACTUAL]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
    Variance = CALCULATE(SUM(Burn[VARIANCE]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
    Expected = CALCULATE(SUM(Burn[BUDGET]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
    • ibarrau's avatar
      ibarrau
      Super User
      6 years ago

      Try using one of my formulas changing the max_date like this:

      Measure =
VAR max_date = 
CALCULATE (
    MAX(Burn[TrxnDate]);
    FILTER(
        ALL(Burn[TrxnDate];Burn[ACTUAL]);
        OR( Burn[ACTUAL]>0 ; Burn[ACTUAL] <> BLANK() )
    )
)
RETURN
--Formula--

       That should be the max date of the actual values. Then you can use that as variable of the other formulas.

      Regards,