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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Hide or put 0 to remaining forecast value for past years

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 !question forum.PNG

 

formula.PNG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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
Resolver III
Resolver III

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
Not applicable

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

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
Not applicable

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 ?

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors