Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors