My data has gaps in the date field (non-consecutive dates).
I need a rate value for a single date from X days ago (30, 60 ...), and if the date X days ago has no value (that day is missing in the data table), then I need to back up in time until I find a value. How do I find it?
My thoughts are to create a temp table consisting of Calendar[Date], 'Rate Index'[Name], 'Rate Data'[Date], 'Rate Data'[Value], then filter the table by a timeframe (startdate: today-X-20, enddate: today-X), then use LastNonBlankValue to get the latest non-blank value.
The startdate and enddate I have as measures (windowStartDate, windowEndDate), and I have a temp table of values, but I'm having trouble getting it all together. Plus, my table has no blank rows for calendar dates with no rate data, but I think that's ok for this function.
I may be waaaay off in my thinking as I'm pretty new to DAX.
For the test data below, assume the user has selected 7/25/2010 as the date to retrieve the rate value. Since 7/25 is not a row in the table, the correct answer would be 1.5% from 7/22/2010.
Thanks in advance for any ideas!
| Name | As of Date | Rate Value |
| Treasury 1 Month | 7/16/2010 | 1.20% |
| Treasury 1 Month | 7/19/2010 | 1.30% |
| Treasury 1 Month | 7/20/2010 | 1.40% |
| Treasury 1 Month | 7/22/2010 | 1.50% |
| Treasury 1 Month | 7/27/2010 | 1.60% |
| Treasury 1 Month | 7/28/2010 | 1.70% |
| Treasury 1 Month | 7/30/2010 | 1.80% |
| Treasury 1 Month | 8/3/2010 | 1.90% |
