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 a forecast table that has all the forecast amounts from January 2017 - December 2017. However, I want to create a measure that only calculate the future months forecast based off the current month (e.g. July 2017 -> December 2017). This is what I have so far, but it's giving me the total forecast (i.e. January -> December 2017). Any ideas on how to fix this?
CALCULATE ( SUM ( OPEX_Forecast[Forecast] ), FILTER ( ALL ( 'Date'[Date] ), MONTH(TODAY() <= MAX ( 'Date'[Date] ) ) ))
Solved! Go to Solution.
@Anonymous
Based on your description, you want to calculate total from current row all the way up to Dec 2017 for all dates later than today. It's like a reverse running total. Right?
You need to use the Total from Today() to End Of Year, minus the running total from Today() to End Of Year. Please refer to measure below:
Forecast = IF ( MAX ( 'Fact'[Date] ) >= TODAY (), CALCULATE ( SUM ( 'Fact'[Amount] ), DATESBETWEEN ( 'Fact'[Date], TODAY (), LASTDATE ( ALL ( 'Fact'[Date] ) ) ) ) - CALCULATE ( SUM ( 'Fact'[Amount] ), DATESBETWEEN ( 'Fact'[Date], TODAY (), LASTDATE ( 'Fact'[Date] ) ) ), BLANK () )
Regards,
bump
@Anonymous
Based on your description, you want to calculate total from current row all the way up to Dec 2017 for all dates later than today. It's like a reverse running total. Right?
You need to use the Total from Today() to End Of Year, minus the running total from Today() to End Of Year. Please refer to measure below:
Forecast = IF ( MAX ( 'Fact'[Date] ) >= TODAY (), CALCULATE ( SUM ( 'Fact'[Amount] ), DATESBETWEEN ( 'Fact'[Date], TODAY (), LASTDATE ( ALL ( 'Fact'[Date] ) ) ) ) - CALCULATE ( SUM ( 'Fact'[Amount] ), DATESBETWEEN ( 'Fact'[Date], TODAY (), LASTDATE ( 'Fact'[Date] ) ) ), BLANK () )
Regards,
Hi @Anonymous,
I made an example and assume you are using the Date table linked to the OPEX_Forecast and add this formula:
Future_Month_forecast = CALCULATE ( CALCULATE(SUM(OPEX_Forecast[Forecast]),OPEX_Forecast[Date]>=TODAY()) )
You can see the result below, by month and in total in a card visual.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis formula does almost what I want. However, I would like it to calculate whole months. For example I want to take actuals from Jan-May and then add the forecast from June -Dec. This formula is only calculating only couple remaining days in June rather than all of June. I am hoping this is just a simple fix.
Hi Jotten, how did you resolve this?? I have the same problem statement.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks, MFelix. However, it's still showing the sum of all the months instead of all the months starting from July.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |