cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paulj1
Helper II
Helper II

Return Nearest Date and Value

This one is perplexing me.... Thought this would be easy... but no.

 

I have a situation where I have a tolerance that changes on an irregular frequency; such as...

Table.JPG

 

What I need is a way of putting a target line on a line chart, or table that shows the target for that day, so in table below the 7% tolerance would apply on all dates 01 March 2020 through to 11 March 2020, then 12 March 2020 to 24 March 2020 would be 5% etc etc.   Daily dates are in a Date Table called Calendar, and tables are joined Date to Date

Table2.JPG

 

I'd like to do this as a measure because Id want to use the daily tolerance in other measures and in graphs (eg do a line chartshowing daily performance vs tolerance for that day), I can do a calculated column in the calendar table if i have to, but would rather not.

 

Ive got this far, but it only returns a value on the specific day not the dates between...

 

DailyTolerance = CALCULATE(MINX(ToleranceTarget,ToleranceTarget[Target]), FILTER(ALLSELECTED('Calendar'),'Calendar'[Date] <= MAX(ToleranceTarget[Date])),VALUES(ToleranceTarget[Target]))

 

Any advice appreciated...

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

Maybe:

 

Sum of Target Column in Date table =
  VAR __TargetDate = MAXX(FILTER(ALL('ToleranceTarget'),'ToleranceTarget'[Date] <= 'Date'[Date]),'ToleranceTarget'[Date])
  VAR __Target = MAXX(FILTER(ALL('ToleranceTarget'),'ToleranceTarget'[Date] = __TargetDate),[Target])
RETURN
  __Target

---------------------------------------

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




View solution in original post

2 REPLIES 2
Greg_Deckler
Super User IV
Super User IV

Maybe:

 

Sum of Target Column in Date table =
  VAR __TargetDate = MAXX(FILTER(ALL('ToleranceTarget'),'ToleranceTarget'[Date] <= 'Date'[Date]),'ToleranceTarget'[Date])
  VAR __Target = MAXX(FILTER(ALL('ToleranceTarget'),'ToleranceTarget'[Date] = __TargetDate),[Target])
RETURN
  __Target

---------------------------------------

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




View solution in original post

Thanks Greg.... worked a treat.... id prefer a measure but a calculated column works... i have about 8 tolerances like this so it gets me going..

 

Thankyou again

 

Keep keep safe in these troubled times

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors