Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
I have a simple table which outlines a measure name, the RAG of that measure against a date (quarter ending date).
The table doesnt have dates for every day, just on the quarter ending date.
I have made a Calendar table to link up to this date.
I have converted the "RAG" in to a value Red = 1, Amber= 2, Green = 3 and anything else = 1000.
I am trying to bring back the "Previous value" so that I can create a Trend calculation, however everything I have tried isnt working.
Any ideas?
Solved! Go to Solution.
Hi,
This calculated column formula works
Column = LOOKUPVALUE('RAG by measure ID'[RAG],'RAG by measure ID'[Measure ID],'RAG by measure ID'[Measure ID],'RAG by measure ID'[Date],CALCULATE(MAX('RAG by measure ID'[Date]),FILTER('RAG by measure ID','RAG by measure ID'[Measure ID]=EARLIER('RAG by measure ID'[Measure ID])&&'RAG by measure ID'[Date]<EARLIER('RAG by measure ID'[Date]))))
Hope this helps.
@Cbutler Can you post that data as text so that I can recreate it in a PBIX file? I don't want to type all that.
Thanks, I've extracted some of the data, can you access this link?
link removed
Hi,
This calculated column formula works
Column = LOOKUPVALUE('RAG by measure ID'[RAG],'RAG by measure ID'[Measure ID],'RAG by measure ID'[Measure ID],'RAG by measure ID'[Date],CALCULATE(MAX('RAG by measure ID'[Date]),FILTER('RAG by measure ID','RAG by measure ID'[Measure ID]=EARLIER('RAG by measure ID'[Measure ID])&&'RAG by measure ID'[Date]<EARLIER('RAG by measure ID'[Date]))))
Hope this helps.
Yes @Ashish_Mathur thank you! 🙂 That's got it.
Out of interest, do you know @Ashish_Mathur @Greg_Deckler why PreviousQuarter didnt work for this calc?
I have done similar measures in other reports and it has worked great, but this one wasnt having any of it and I don'y understand why....?
Thanks again for your help both.
@Cbutler - Should be something along the lines of:
Previous rag =
VAR __Date = [Date]
VAR __Measure = [Measure]
VAR __PreviousDate = MAXX(FILTER('Table',[Date]<__Date && [Measure]=__Measure),[Date])
RETURN
MAXX(FILTER('Table',[Date]=__PreviousDate && [Measure]=__Measure),[RAG])
@Greg_Deckler
I tried this but didnt work 😞
@Cbutler So can you post your sample data as text?
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |