Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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.
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.
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |