Running total modification

[Anonymous]

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 2020	20
29 Feb 2020	20
31 Jan 2020	20
30 Apr 2020	20
30 Mar 2020	20

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

31 Jan 2020	20
29 Feb 2020	40
30 Mar 2020	60
31 Apr 2020	80

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

[amitchandak]

@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]

@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]

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 · Accepted solution]

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]

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]

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]

Hi @Anonymous ,

The second one, this formula handles the duplicated value.

Ricardo

[Anonymous]

Unfortunately it still sums everything up for the same month, although only for previous months, not future. Did I do something wrong:

DI Target Running (test) = CALCULATE(
                  SUMX('DI Impact','DI Impact'[DI Target.DI Target Amount] / CALCULATE(COUNT('DI Impact'[DI Impact Date]))),
                  FILTER(ALL('DI Impact'[DI Impact Date]),'DI Impact'[DI Impact Date] <= MAX('DI Impact'[DI Impact Date])))