cancel
Showing results for
Did you mean:
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...

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

1 ACCEPTED SOLUTION
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``````

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

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

Proud to be a Super User!

2 REPLIES 2
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``````

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

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

Proud to be a Super User!

Helper II

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

Announcements

#### Microsoft named a Leader in The Forrester Wave

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