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 to amend for two specific scenarios:

 

  • My running total agreggates "actuaL" (vs forecast) numbers and I am hoping to hide the value for future months. I have tried different ways to adjust my measure but to no avail.

 

 

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"
)

 

 

  • I have another running total measure which reads against a table where unfortunately the monthly values are repeated and I would like to only agreggate the first value of each month. Below is a table of the structure of my data
31 Jan 202020
29 Feb 202020
31 Jan 202020
30 Apr 202020
30 Mar 202020

 

I would effectively like my meeasure to return the following when visualized against the DI Impact Date column:

 

31 Jan 202020
29 Feb 202040
30 Mar 202060
31 Apr 202080

 

ANd here is my formula:

 

 

DI Target Running = CALCULATE(
                  SUM ('DI Impact'[DI Target.DI Target Amount]),
                  FILTER (
                    ALL ('DI Impact'),'DI Impact'[DI Impact Date] <= MAX ('DI Impact'[DI Impact Date])
                  )
)

 

 

 

Thanks in advance for any suggestion you can provide!

OF

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

8 Replies

  • 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"

)