Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I´m having trouble finding a solution to the following:
In my company´s budget, we have "three versions" in the database:
1- Planned: have values for all 12 months
2- Actual: have values "up to date" (let´s assume values until month 5)
3- Forecast: future values to be compared with annual plan and future "actuals".
Problem: in the Forecast version, I need the past months values to be replaced by what actually happened (could have multiple lines, so needs to be a sum).
I tried to make an example below what I need:
Table 1 is what I have. Month 1 to 5 of forecast is not important for me anymore, and needs to be replaced by the sum of "Actual" for each month.
Values | Version | Month |
2,50 | Actual | 1 |
3,00 | Actual | 1 |
1,00 | Actual | 2 |
1,50 | Actual | 3 |
2,00 | Actual | 3 |
2,50 | Actual | 3 |
2,50 | Actual | 4 |
2,00 | Actual | 5 |
3,00 | Planned | 1 |
2,00 | Planned | 2 |
4,00 | Planned | 3 |
3,50 | Planned | 4 |
6,00 | Planned | 5 |
4,30 | Planned | 6 |
2,30 | Planned | 7 |
4,00 | Planned | 8 |
2,30 | Planned | 9 |
2,00 | Planned | 10 |
5,00 | Planned | 11 |
2,30 | Planned | 12 |
3,00 | Forecast | 1 |
2,00 | Forecast | 2 |
4,00 | Forecast | 3 |
3,50 | Forecast | 4 |
6,00 | Forecast | 5 |
12,00 | Forecast | 6 |
13,00 | Forecast | 7 |
13,00 | Forecast | 8 |
11,00 | Forecast | 9 |
14,00 | Forecast | 10 |
15,00 | Forecast | 11 |
11,00 | Forecast | 12 |
The table below is what I´m expecting. Notice that the Forecast values of months 1 to 5 are replaced by the sum of each period that already happened, in version "Actual".
Values | Version | Month | Year |
2,50 | Actual | 1 | 2018 |
3,00 | Actual | 1 | 2018 |
1,00 | Actual | 2 | 2018 |
1,50 | Actual | 3 | 2018 |
2,00 | Actual | 3 | 2018 |
2,50 | Actual | 3 | 2018 |
2,50 | Actual | 4 | 2018 |
2,00 | Actual | 5 | 2018 |
3,00 | Planned | 1 | 2018 |
... | ... | ... | ... |
5,50 | Forecast | 1 | 2018 |
1,00 | Forecast | 2 | 2018 |
6,00 | Forecast | 3 | 2018 |
2,50 | Forecast | 4 | 2018 |
2,00 | Forecast | 5 | 2018 |
12,00 | Forecast | 6 | 2018 |
... | ... | ... | ... |
I do have single measures that work for me, as shown bellow. However, I want to use the Waterfall Chart, and I cannot have different measures. I have to have a single column with different versions, in order to work.
Forecast measure
Forecast = CALCULATE( SUM('Table1'[Values]); FILTER( 'Table1'; ('Table1'[Version]; 'Table1'[Month]) IN { ( "Actual"; 1 ); ( "Actual"; 2 ); ( "Actual"; 3 ); ( "Actual"; 4 ); ( "Actual"; 5 ); ( "Forecast"; 6 ); ( "Forecast"; 7 ); ( "Forecast"; 8 ); ( "Forecast"; 9 ); ( "Forecast"; 10 ); ( "Forecast"; 11 ); ( "Forecast"; 12 ) } )
Planned measure (similar for Actual)
Planned = CALCULATE( SUM('Table1'[Values]); 'Table1'[Version] IN { "Planned" } )
Thank you for the help!
Hi @Anonymous,
Try this measure:
Measure = SWITCH ( TRUE (); CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Actual" ) = BLANK () && MAX ( 'Fact'[Version] ) <> "Planned"; CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Forecast" ); CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Actual" ) <> BLANK () && MAX ( 'Fact'[Version] ) <> "Planned"; CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Actual" ); CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Planned" ) )
Should return what you need.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you @MFelix,
I like your approach, and it would solve my problem if I didn´t need to use the "waterfall chart".
That means I need this "forecast", but I need to keep my "Planned" values in a different version, so I´m guessing I cannot use a measure in Waterfall Chart. I need to use a column of values in "Y Axis" with "version" as category.
Is there a way to transform this measure formula into a "calculated column" of values, in which "version" would be set to "forecast" for all months?
Thank you once again,
Eduardo
I found a solution modifying my SQL Query. It works, but I still wish I could find the same solution working directly in PowerBI Desktop modelling with DAX.
Below is the SQL Query that worked, so it might help you find the solution in DAX.
[...] CASE WHEN MONTH < 6 AND VERSION = 'FORECAST' THEN (SELECT SUM(ACTUAL.VALUES) AS "Values" FROM SAP_CO.ACTUAL WHERE ACTUAL.COSTCENTER = FORECAST.COSTCENTER AND ACTUAL.MONTH = FORECAST.MONTH AND ACTUAL.YEAR = FORECAST.YEAR AND ACTUAL.CLASSCOST = FORECAST.CLASSCOST ELSE FORECAST.VALUES AS "Values", [...]
Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |