Forecast consisting of actual + forecast filtered by FiscalYear

[Kronhjort]

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:

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

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 

[Kronhjort]

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]

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:

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):

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!

[Kronhjort]

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. 

[v-janeyg-msft]

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