Hide or put 0 to remaining forecast value for past years

[Anonymous]

Hey there,

I have "planned" and "actual" values for projects. I'm calculating the "remaining forecast" until 2025, works fine, problem is I also have remaining forecast for 2020....and this will be the case next year and the year after and so on.

I'm writng an IF condition to put 0 for starters for 2020 in my Remaining forecast formula, but there must be something wrong with the synthax cause it doesn't recognize my [Year] column.
But as I said, I'm going to need it for past years in general, so I would then need to optimize my formula...

Would you have any ideas of what am i doing wrong and/or how to write it otherwize ?

Thanks a lot in advance !

[Anonymous · Accepted solution]

Hey there,

For anyone who might need it, I fixed my problem by making an extra measure (i know it's not good practice to add intermediate measures though) in the table that recognized my Year column :

Forecast Remaining = IF(MIN('Value Log Report'[Year])>=YEAR(Today()), [Remaining Forecast for chart], BLANK())

This works nice, I was insipred by another solved case here on the Forum : Hiding future values for TotalYtd for current year against a forecast 

Cheers

[AntoineTRICHET]

Hi @Anonymous 

In think you are in the wrong sections. For DAX formula the good section is Desktop (and not Power Query).

To answer your issue, for me you have to check if the type of your column Year is whole number. Then you can use the following formula :
Remaining Forecast for chart = IF('Value Log Report'[Year]>YEAR(TODAY())-1,([Forecast Cumulative for Chart] - [Actual Cumulative for Business]),0)

Please accept it as a solution if it solves your issue

[Anonymous]

Thanks for your reply !

My column Year is a whole number, and I am in Desktop rather than Power Query...but still it doesn't work 😕 but I see your idea and I think if it weren't for this problem with the Year it's the thing to do

[AntoineTRICHET]

Did your try to analyze your formula like that (it can help you to identify what is not working in your formula, the test or the calculation) :
Remaining Forecast for chart = IF('Value Log Report'[Year]>YEAR(TODAY())-1,1,0)

[Anonymous]

Yes, it's the same. I also tried to do a calculated column instead of a measure, but I'm not sure it work either.

Do you think that maybe my Actual Cumulative and Forecast Cumulative should be columns as well ?

[AntoineTRICHET]

Hi again
Maybe, that is difficult to say with the information you provided