Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I am working on a Power BI dashboard that is forecasting values up until 2030, depending on projects that are under implementation between 2022 to 2030.
Does anyone know of a DAX formula, calculated column, or any solution to the problem above?
Many thanks
@Anonymous
Following are my assumptions
Then you may try the following measure
Forecast Values =
VAR CurrentQuarter =
MAX ( 'Date'[YearQuarter] )
VAR LastActualDate =
CALCULATE ( MAX ( Actual[Date] ), REMOVEFILTERS () )
VAR LastActualQuarter =
CALCULATE (
MAX ( 'Date'[YearQuarter] ),
'Date'[Date] = LastActualDate,
REMOVEFILTERS ()
)
VAR ALLYearsAndQuarters =
SUMMARIZE ( ALL ( 'Date' ), 'Date'[Year], 'Date'[YearQuarter] )
VAR T1 =
FILTER (
ALLYearsAndQuarters,
'Date'[YearQuarter] >= LastActualQuarter
&& 'Date'[YearQuarter] <= CurrentQuarter
)
VAR T2 =
ADDCOLUMNS (
T1,
"@Actual", [Actual Values],
"@Saving", [Estimated Saving From Projects]
)
RETURN
IF (
CurrentQuarter < LastActualQuarter,
[Actual Values] - [Estimated Saving From Projects],
SUMX ( T2, [@Actual] - [@Saving] )
)
Hi @tamerj1 ,
since it seems that your advices hepled user, I have a similar problem.
Let me explain a little bit the context, since I'm not allowed to share files.
Forecast EOH =
VAR CurrentWeek =
MAX('date'[Week Rank])
VAR LastWeek =
CALCULATE(MAX('date'[Week Rank]) -1)
VAR LastActualWeek =
CALCULATE (
MAX ( 'date'[Week Rank] );
'date'[Week Rank] = LastWeek;
REMOVEFILTERS ()
)
VAR ALLWeeks =
SUMMARIZE ( ALL ( 'Date' ); 'date'[Week Rank] )
VAR T1 =
FILTER (
ALLWeeks;
'date'[Week Rank] >= LastActualWeek
&& 'date'[Week Rank] <= CurrentWeek
)
VAR T2 =
ADDCOLUMNS (
T1;
"@Actual"; [Last Week EOH];
"@Fct Sales"; [This Week Sales]
)
RETURN
IF (
[This Week EOH] > 0;
[This Week EOH] ;
SUMX ( T2; [@Actual] - [@Fct Sales] )
)
I am struggling with this part and I can't finish my project without it.
Can I please ask for your help? It would be super if you would be able to help me through this.
Thank you very much in advance
Tommy
Hi @tamerj1
Thank you very much for the prompt response! Sorry i took so long to reply as well, havent been able to get back to this piece of work since last week.
I tried your forumla and it seems to work if i alter the above fromula for just Year instead of YearQuarter as seen below:
This is what i want which is perfect.
However when using the YearQuarter fromula you have given, it doesnt seem to be working in the same way as shown below:
Is there a way so that I am able to end up with the same value at the end as the Years Table?
Please let me know if you need anything else clarifying.
Many thanks
@Anonymous
It is not easy to write such a code without working on a sample file. I'm trying to imagine how should be the DAX that can produce such result. However, Please try the following (sorry for the bad format as the code format does not work on the phone)
Forecast Values Quartets =
VAR CurrentQuarter =
MAX ( 'Date'[YearQuarter] )
VAR LastActualDate =
CALCULATE ( MAX ( Actual[Date] ), REMOVEFILTERS () )
VAR LastActualQuarter =
CALCULATE (
MAX ( 'Date'[YearQuarter] ),
'Date'[Date] = LastActualDate,
REMOVEFILTERS ()
)
VAR ALLYearsAndQuarters =
SUMMARIZE ( ALL ( 'Date' ), 'Date'[Year], 'Date'[YearQuarter] )
VAR T1 =
FILTER (
ALLYearsAndQuarters,
'Date'[YearQuarter] >= LastActualQuarter
&& 'Date'[YearQuarter] <= CurrentQuarter
)
VAR T2 =
ADDCOLUMNS (
T1,
"@Actual", CALCULATE ( [Actual Values], ALL ( 'Date'[YearQuarter] ) ),
"@Saving", [Estimated Saving From Projects]
)
RETURN
IF (
CurrentQuarter < LastActualQuarter,
[Actual Values] - [Estimated Saving From Projects],
SUMX ( T2, DIVIDE ( [@Actual] - [@Saving], 4 ) )
)
Hi @tamerj1
Unfortunately I am unable to share the sample file, as it contains protected information, but I can try to explain the situation better.
I tried the most recent code that you gave, and it doesn’t seem to subtract the correct values. It looks like the formula that you gave divides the difference between the actual values and the estimated savings from projects by 4, but the data is already split by quarters already this won’t show the correct values.
However I think I know what is the solution that I’m trying to work towards. As I mentioned before, the years formula works just fine, as seen in the table below;
The Quartets formula that you gave doesn’t seem to subtract the correct values, as seen below
If we go back to the original formula that you gave for the quarters calculation, the subtraction was correct, but because the data was split by quarters, and the number that was used to in the subtraction was wrong. Below is the table for the original quarters table:
In the above table, the subtraction starts happening from 2022/Q1 where the value is 52.48, but in the years table, in the Years table, the subtraction happens from 2022 where the value is 176.1. Is there a way of making it so that within the quarters formula, we start subtracting from the total years sum? So in the above table, we start of with the summation of the previous year value, and start subtracting from that. This would stop the problem of the values becoming negative.
Also as a side note, is there any way of somehow excluding the ‘estimated savings from projects’ in 2022 as we already have the 2022 full year values? Is it possible to include this within the formulas calculating the forecasting values?
Please let me know if you understand and need any other clarification.
Again many thanks for your help!
Hi @Anonymous
Are Year and YearQuarter from a Date table?
Sorry i forgot to mention this, yes year and year quarter are from a date table
@Anonymous
Ok then please try my proposed solution in the other reply.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |