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
Cbutler
Helper III
Helper III

Previous value not working??

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.
 

Previous RAG.PNG


Any ideas? 

1 ACCEPTED 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.

Untitled.png


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

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

@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.


@ 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...

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.

Untitled.png


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

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. 

Greg_Deckler
Super User
Super User

@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])

@ 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...

@Greg_Deckler 

I tried this but didnt work 😞

*Previous rag =
VAR __Date = ALL('QR Measures'[Date])
VAR __Measure = ALL('QR Measures'[Id])
VAR __PreviousDate =
MAXX ( FILTER ( 'QR Measures', 'QR Measures'[Date] < __Date && 'QR Measures'[Id] = __Measure ), 'QR Measures'[Date] )
RETURN
MAXX (
FILTER ( 'QR Measures', 'QR Measures'[Date] = __PreviousDate && 'QR Measures'[Id]= __Measure ),
'QR Measures'[RAG]
)

@Cbutler So can you post your sample data as text?


@ 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...

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.