cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

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 🙂

1 ACCEPTED SOLUTION
Microsoft Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
New Member

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

New Member

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

Microsoft Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

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?

Microsoft Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

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

Microsoft Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

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