cumulative running total formula that doesnt continue to display past the values entered

[Nikki]

i have to show actuals, budget, forecast on a line/bar chart. the issue is that values for "actuals" stop in june but continue to show the same value till december. see pic. i created the running total using quick measures - very cool feature. i just need to add something to this formula i think to stop it running.. please help 🙂

[DiegoPisoni]

Hi, 

I think that I have a similar problem.

I have a table that sum the velue of Actual with the value of badget, it is ok untill July (where i have every value), from august he does Actual (from Jen to Jul) - Badget (form Jen to Aug), and obviously is incorect.

What and how can i do?

P.s. this is the formula

         Fatt. ytd running total in Desc. Mese = CALCULATE(
SUM('THUN_SI'[Fatt. ytd]);
FILTER(
CALCULATETABLE(
SUMMARIZE('DescMese'; 'DescMese'[Mese]; 'DescMese'[Desc. Mese]);
ALLSELECTED('DescMese')
);
ISONORAFTER(
'DescMese'[Mese]; MAX('DescMese'[Mese]); DESC;
'DescMese'[Desc. Mese]; MAX('DescMese'[Desc. Mese]); DESC
)
IF ('THUN_SI'[Fatt. ytd]) <>0;
)
)

Thank you

[DiegoPisoni]

Hi, 

I think that I have a similar problem.

I have a table that sum the velue of Actual with the value of badget, it is ok untill July (where i have every value), from august he does Actual (from Jen to Jul) - Badget (form Jen to Aug), and obviously is incorect.

What and how can i do?

P.s. this is the formula

         Fatt. ytd running total in Desc. Mese = CALCULATE(
SUM('THUN_SI'[Fatt. ytd]);
FILTER(
CALCULATETABLE(
SUMMARIZE('DescMese'; 'DescMese'[Mese]; 'DescMese'[Desc. Mese]);
ALLSELECTED('DescMese')
);
ISONORAFTER(
'DescMese'[Mese]; MAX('DescMese'[Mese]); DESC;
'DescMese'[Desc. Mese]; MAX('DescMese'[Desc. Mese]); DESC
)
IF ('THUN_SI'[Fatt. ytd]) <>0;
)
)

Thank you

[Anonymous]

Hi @Nikki，

Do you have blank “Actual” values in latter Month(July, August…December) and the Month field is a Date field?  If so, please create ActualsRunning measure using the following measure, or just add the “&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1” part in FILTER function in your original measure and check the result.

ActualRunning = CALCULATE(SUM(Financials[Actuals]),FILTER(ALLSELECTED(Financials[Month]),Financials[Month]<=MAX(Financials[Month])&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))

Thanks,
Lydia Zhang

[Nikki]

thanks but the issue is that Actuals is a measure and not a column so it didnt like the calculate(SUM bit. is there another way to do it?

[Anonymous]

Hi @Nikki,

You mean that [ProjectActuals] is a measure, right? If so, what is the result when you use one of the following formulas to create your measure?

ActualRunning = CALCULATE(Financials[ProjectActuals],FILTER(ALLSELECTED(Financials[Month]),Financials[Month]<=MAX(Financials[Month])&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))

ActualRunning = CALCULATE(Financials[ProjectActuals],FILTER(ALLSELECTED(Financials[Month]), ISNOORAFTER(Financials[Month],MAX(Financials[Month]),DESC)&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))

Thanks,
Lydia Zhang

[Nikki]

yes it is a measure you are right. i created it from the quick measures. below is a screenshot. hope this helps - thanks heaps

[Anonymous · Accepted solution]

Hi @Nikki,

I note that you have renamed ProjectActuals measure to Project Actuals.

Right-Click your table and select "New measure", then enter one of following formula and check the result.

ActualRunning = CALCULATE(Financials[Project Actuals],FILTER(ALLSELECTED(Financials[Month]),Financials[Month]<=MAX(Financials[Month])&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))

ActualRunning = CALCULATE(Financials[Project Actuals],FILTER(ALLSELECTED(Financials[Month]), ISNOORAFTER(Financials[Month],MAX(Financials[Month]),DESC)&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))

If the issue still occurs, please share sample data of your table or share me the PBIX file.

Thanks,
Lydia Zhang

[Nikki]

also this is the formula for project actuals ( the measure)

Project Actuals = [DateValue for Project Costs for Actuals - Capex] + [DateValue for Project Costs for Actuals - Opex]

and below are formulas for both above too - also measures 🙂

DateValue for Project Costs for Actuals - Capex =
CALCULATE(
 'Financials'[DateValue for Project Costs],
 TREATAS({("Actuals - Capex")}, 'Financials'[Title])
)

DateValue for Project Costs for Actuals - Opex =
CALCULATE(
 'Financials'[DateValue for Project Costs],
 TREATAS({("Actuals - Opex")}, 'Financials'[Title])
)