Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Nikki
Helper II
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 🙂

 

2017-05-12_3-01-15.png

 

1 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

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.

View solution in original post

8 REPLIES 8
DiegoPisoni
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

 

DiegoPisoni
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

 

v-yuezhe-msft
Microsoft Employee
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))
1.PNG

 

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.

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?

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.

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

2017-05-16_16-02-49.png

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.

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors