cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Accepted Solutions
Super User
Super User

Re: Return the last value with specific conditions

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.

5 REPLIES 5
Marshman99ca Visitor
Visitor

Re: Return the last value with specific conditions

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.

Super User
Super User

Re: Return the last value with specific conditions

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.

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Return the last value with specific conditions

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.

Re: Return the last value with specific conditions

Perfect thanks @Ashish_Mathur

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

 

Thanks Smiley Happy

Super User
Super User

Re: Return the last value with specific conditions

You are welcome.