Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running total modification

Hi PowerBI community, First off, I hope you are all well and safe in these challenging times.   I was hoping some of you might be able to assist me with a working "running total" formula that I ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi all,

     

    Unfortunately none of these solutions worked 😞 What I ended-up doing:

     

    • future month as blank: I gave up
    • only suming the first line against a repetitive month: I moved the columns in a separate column with no repeats and applied a simple running total measure and created a relationship back to the first table.

    THanks all for trying to help 🙂

    OF

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , try the first formula. Or try the second one with a date calendar

DI Impact Actual Running Total = CALCULATE(
                  SUM ('DI Impact'[DI Impact]),
                  FILTER (
                    ALL ('DI Impact'),'DI Impact'[DI Impact Date] <= MAX ('DI Impact'[DI Impact Date])
                  && 'DI Impact'[DI Impact Type]="Actual"
)
)


Or 

DI Impact Actual Running Total = CALCULATE(
                  SUM ('DI Impact'[DI Impact]),
                  FILTER (
                    ALL ('Date'),'Date'[Date] <= MAX ('DI Impact'[Date]))
                  ,'DI Impact'[DI Impact Type]="Actual"

)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak , thank you for the suggestion. You actually gave me the idea to further amend the formula to be more precise. 

 

Unfortunately it still shows numbers for months in the future whereas I was hoping to show "null"

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try for the future month as 0

    DI Impact Actual Running Total = CALCULATE(
                  SUM ('DI Impact'[DI Impact]),
                  FILTER (
                    ALL ('DI Impact'),'DI Impact'[DI Impact Date] <= MAX ('DI Impact'[DI Impact Date])
					&& 'DI Impact'[DI Impact Date] <=today()
                  && 'DI Impact'[DI Impact Type]="Actual"
)
)


Or 

DI Impact Actual Running Total = CALCULATE(
                  SUM ('DI Impact'[DI Impact]),
                  FILTER (
                    ALL ('Date'),'Date'[Date] <= MAX ('DI Impact'[Date])
					&& 'Date'[Date]<=today()
					)
                  ,'DI Impact'[DI Impact Type]="Actual"

)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi all,

       

      Unfortunately none of these solutions worked 😞 What I ended-up doing:

       

      • future month as blank: I gave up
      • only suming the first line against a repetitive month: I moved the columns in a separate column with no repeats and applied a simple running total measure and created a relationship back to the first table.

      THanks all for trying to help 🙂

      OF

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    You can try this code to calculate running total without group the values before:

     

    CALCULATE(SUMX(Table_2; Table_2[Value] / CALCULATE(COUNT(Table_2[Date]))); FILTER(ALL(Table_2[Date]); Table_2[Date] <= MAX(Table_2[Date])))
     
    I hope it helps,
     
    Ricardo
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi camargos88 , do you suggest I use this formula for my first or second problem? I'm trying to convert it to make it work 🤔

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        Hi Anonymous ,

         

        The second one, this formula handles the duplicated value.

         

        Ricardo