Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 🙂
Solved! Go to 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
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
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
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
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
yes it is a measure you are right. i created it from the quick measures. below is a screenshot. hope this helps - thanks heaps
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
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])
)
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
99 | |
39 | |
30 |