Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Kronhjort
Frequent Visitor

Forecast consisting of actual + forecast filtered by FiscalYear

Hello

I am working on a forecast model for Co2 reductions.
I have used this method from SQLBI before: https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/

It has been working well but, in this case, I want to use it on FiscalYear.

I expect to get FY22 to consist of actual data until 15 October 2021 and then forecast for the rest of the FY.

This screenshot clearly shows that it is not happening:

Kronhjort_0-1643096063031.png

 


A detailed dive into the data shows that Remaining Forecast is blank in the rest of FY or FQ where actual data is present:

Kronhjort_1-1643096063038.png

 


I am sure this is a simple mistake related to the filter/keepfilter in remaning forecast. I cannot seam to get head around it.

An important detail might be that FiscalYear is sorted by FiscalYearNum. Therefore, both must be a part of a filter.

 

I have attached a link to a PIBX file with dummy data and relevant measures  

Link to file on Wetranfere 

4 REPLIES 4
Kronhjort
Frequent Visitor

Hi Janey 

 

I will try to make a more simple sample. It is a quite complicated solution.

 

Maybe it is better to spilt the measure in to 3: 

1. Actual data

2. Forecast current year

3. Forecast other years 

ValtteriN
Super User
Super User

Hi,

I would try to use ALL instead of REMOVEFILTERS in your first variable and additionally in these kind of calculations I would consider using a separate "cut-off" slicer for the LastDateWithJob. e.g. use a column from a calendar table and name it something like "Forecast after this date". With this it is eay to test whether or not the logic is working.

Here is my example of doing similar calculations:

Data:

ValtteriN_0-1643098476060.png



Dax:

ACT+Forecast (cut-off) =
var cut_off = MAX('Calendar'[Date])
var cdate = MAX('Forecast+ACT'[Date])
var _act = CALCULATE(SUM('Forecast+ACT'[Act]),ALL('Forecast+ACT'),'Forecast+ACT'[Date]<=cut_off,'Forecast+ACT'[Date]<=cdate)
var _forecast = CALCULATE(SUM('Forecast+ACT'[Forecast]),ALL('Forecast+ACT'),'Forecast+ACT'[Date]>cut_off,'Forecast+ACT'[Date]<=cdate)
return
_act+_forecast
 
End result (note that I don't have a relationship between my calendar and fact table):
ValtteriN_1-1643098548503.png


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you for your reply 

The first part of the code returs the last data for all rows as expexted (see screenshot)

The last part is still not working. I cannot see how I translate the code you have made to fit my case. 

 

Kronhjort_0-1643196082332.png

 

Hi, @Kronhjort 

 

I think what @ValtteriN  means is to let you calculate the value of current and forecast separately first, so that it is easy to determine where the problem is.

 

I checked your link and it's so messy that I can't even find some measures, it is very likely that there is a problem with the measure inside measure. If you don't simplify the sample, I think it's hard for anyone to give you a substantive advice...It is better for you to rule out the problem step by step.

 

Best Regards,
Community Support Team _ Janey

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.