Return Nearest Date and Value

[paulj1]

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

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

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

[Greg_Deckler · Accepted solution]

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

[paulj1]

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