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.
I have the table below:
Month Budget Realized
Oct $489943,13 $515707,20
Nov $883368,63 $643675,04
Dec $637668,28 $681661,99
Jan $1931667,93 $626560,46
Fev $1289529,29 $748939,90
Mar $2372920,47 $739028,26
Apr ($289462,65) $667915,29
May $759686,98 $920683,26
Jun $603624,00 $800318,55
Jul $633832,05 $697527,81
Aug $655151,28 $51019,12
Sep $556045,26 $0,00
1) average of realized = sum total realized / quantity of distinct months where sum done of month> 0;
Which would be = 7,093,036.88 / 11 = 644,821.53I need two pieces of information:
2) Forecast at closing = Sum of the realized + result of the application of the forecast function for the remaining months.
For example:
I have 10 months of execution and 2 is missing by the end of the year. So I have to make a prognosis of the others according to the ones I already have, that is, to make the projection for the others. In the table above, I have 11 months running and 1 is left, I need the projection of that last month according to what has already been done.
Can someone help me?
Tnks,
Edson.
Solved! Go to Solution.
Hi @edson_souza,
Create the following measures:
Forecast_Total = IF ( SUM ( Average_Forecast[Realized] ) = 0; CALCULATE ( [Average realized]; ALLSELECTED ( Average_Forecast ) ); SUM ( Average_Forecast[Realized] ) )
Forecast = IF ( SUM ( Average_Forecast[Realized] ) = 0; CALCULATE ( [Average realized]; ALLSELECTED ( Average_Forecast ) ); SUMX ( SUMMARIZE ( Average_Forecast; Average_Forecast[Month]; "Forecasted"; Average_Forecast[Forecast_Total] ); [Forecasted] ) )
The first one is just auxiliary to calculate the total value for the year, then just add the Forecast to your table should give expected result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @edson_souza,
First of all I would make your month column into a date column with the last day of the month as a value this will allow you to use the Date features and the YTD QTD MTD calculations then add this to your model and you should get what you need:
Average realized = VAR Realized = TOTALYTD ( SUM ( Average_Forecast[Realized] ); Average_Forecast[Month] ) VAR Months_Count = CALCULATE ( DISTINCTCOUNT ( Average_Forecast[Month] ); FILTER ( ALLSELECTED ( Average_Forecast ); Average_Forecast[Month] <= MAX ( Average_Forecast[Month] ) ); FILTER ( ALLSELECTED ( Average_Forecast ); Average_Forecast[Realized] > 0 ) ) RETURN DIVIDE ( Realized; Months_Count ) FORMULA ALSO WORKS FOR MORE THAN ONE ROW PER MONTH
As you can see below I have a column with the Average and also the count of months that is the second part of the divide so you can see that the months with 0 are not counted on the division. Used the Hierarchy to only show month in the calculations.
Regarding your second calculation not sure what you want and how you want to calculate. Where do you get the actuals and what value you want to assume for the month?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you, @MFelix.
Item 1 solved with the formula that you sent, although the values were not equal monthly, the general average was correct (644.821,53).
For item 2, the forecast would be: when realized, the same as realized. When there is no, equal to the monthly average already found. According to the table below:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
I tested it this way, but the unrealized month continues with a value equal to 0.00.
I noticed in your table "Average realized" has value in every month, mine only where realized.
I was able to apply the monthly average, but when I put the IF (Realized = 0; Average; Realized) the TOTAL does not add all the values.
How can I do it?
Note that the Forecast total is the same as Realized, but should also add up to 644,821.53, with the result equal to 7,737,858.41.
Hi @edson_souza,
Create the following measures:
Forecast_Total = IF ( SUM ( Average_Forecast[Realized] ) = 0; CALCULATE ( [Average realized]; ALLSELECTED ( Average_Forecast ) ); SUM ( Average_Forecast[Realized] ) )
Forecast = IF ( SUM ( Average_Forecast[Realized] ) = 0; CALCULATE ( [Average realized]; ALLSELECTED ( Average_Forecast ) ); SUMX ( SUMMARIZE ( Average_Forecast; Average_Forecast[Month]; "Forecasted"; Average_Forecast[Forecast_Total] ); [Forecasted] ) )
The first one is just auxiliary to calculate the total value for the year, then just add the Forecast to your table should give expected result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |