cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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/

View solution in original post

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 IV
Super User IV

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors