Simple IF statement not working. Help please!!!

[tbobolz]

Hi All,

I have used this system to auto find the current fiscal year many times in the past; however, today I just can't figure out why it is not working! The column in the below pbix file, "IsMaxCFY", seems correct but it returns true (or "1") for everything. I think I have clean fields, proper formatting. Am I just too tired tonight? 

https://drive.google.com/open?id=1FaCac9Ll6HObZ9z3Qma-K11M8b_rW3zK

Thanks for any help you can offer.

Terry

[Ashish_Mathur]

Hi,

In a calculated column formula (IsMaxCFY), you are referring to a measure (CFY).  Why are you doing so?  Please describe your question and show the expected result.

[tbobolz]

Hi Ashish,

Thanks for the reply.  Measure CFY is meant to find the MAX year, which it does fine in the dataset. (2019)

Then column IsMaxCFY should compare CFY to my fiscal years field with an IF statement and return a "1" if it is 2019, and "0" if the year is anything other. 

I then use IsMaxCFY as a filter to set all dashboard elements to the current fiscal year, and with each new year, this will automatically update. This has worked in many other senarios, but for some reason, this thime it is not working.  I see Matt A has offered a solution too, that I have not yet applied. (Thanks Matt). But I am still curious why this method has failed me this time.

In addtional, I'll use the same method to find the previous year, current/previous month to build YOY YTD comparison that once I connect to a gateway file, everything is auto set to update and correct its self as the years and month change.

Thanks for you help.

[Ashish_Mathur]

Hi,

Why not just drag the FY to the Report filter section and select 2019.  All visuals across all pages would be filtered by 2019.

[tbobolz]

True, however, when the data starts to include FY20, then the file would not automatcally update to the new year.

[MattAllington · Accepted solution]

DAX is a challenging language to learn.  It seems straight forward but it is like an onion - the more you peel back the layers, the more you find new layers.

Here is the formula you seek

IsMaxCFY = if('Registration - Export'[Fiscal Year]=MAX('Registration - Export'[Fiscal Year]),1,0)

The issue is complex and has to do with context transition.

There are lots of things I would recommend you do differently.  Perhaps work through the knowledge base on my website

https://exceleratorbi.com.au/knowledge-base/

Be sure to read the best practices https://exceleratorbi.com.au/best-practices-power-pivot-power-query-power-bi/

[tbobolz]

Thanks Matt,

Yes, such a simplier solution, I appreciatet your time and I WILL read through knowledgebase to grow. 

Terry