Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |