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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
robstewart89
Advocate IV
Advocate IV

Return the last value with specific conditions

Not sure really how to title this one

 

I have a table that i want to add a calculated column to, to give me a Gold/Silver/Bronze category (already created) by the store ID. So in the image below, i have the store (Column: ID) but i also have the date, and the Cycle Name. The blue column on the end is what I want to achieve.

 

I basically want to return the GSB for each store but only showing the last instance for each Cycle. So for Store 100001 in P02, it should be Silver as that was the last date in P2 that had a GSB agains it. So far in the P03 it is Under Bronze (as seen on the 27th Feb) anything else should be blank.

 

Does this make sense? Apologies for my paint drawing skills

 

Power BI check.PNG

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

Try this calculated column formula

 

=IF(Data[Date]=CALCULATE(MAX(Data[Date]),FILTER(Data,Data[ID]=EARLIER(Data[Date])&&Data[CycleName]=EARLIER(Data[CycleName]))),Data[GSB],BLANK())

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

Try this calculated column formula

 

=IF(Data[Date]=CALCULATE(MAX(Data[Date]),FILTER(Data,Data[ID]=EARLIER(Data[Date])&&Data[CycleName]=EARLIER(Data[CycleName]))),Data[GSB],BLANK())

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Perfect thanks @Ashish_Mathur

Slight error as it should be ID=EARLIER(ID) which I amended and now all is working

 

Thanks 🙂

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

You didn't supply data that is easily copied so I'm going to refer you to one of my articles. Otherwise, I probably could have written the formula for this in about 5 minutes. But I'll get it wrong without test data and I'm too lazy to create it by hand.

 

http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

 

The trick is going to be using ALL as well as EARLIER. Basically, you want to grab ALL of the table and then FILTER that to the current CycleName (EARLIER) and then you want to grab the GSB from the row with th MAXX date. Basically that's what you want to do, the article shows an example of something similar on an unrelated topic.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Marshman99ca
Regular Visitor

I don't think you should be trying to do this with a calculated column. It sounds like you need to create a new measure, or several measures, one for each store. 

How many stores are there in the data?

You could create a DAX formula based on the conditions of; Store, Latest Date, and value.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.