Can datepicker on calendar object choose closest valid date?

[Karpenter1]

Hi, using the calendar object for the first time and it seems to be working well with the exception of the date picker. If I select a date on the date picker that doesn't have any orders, there is no selection. Is there a way I can modify this behaviour so that the closest valid date is selected?

[Ksrinivasan]

hi,

for your understanding

DailySales:  // actual my data
LOAD
SalesDate,
SalesVolumn
FROM [lib://REPORT EXTRACTION /WIP_Report/Excel and Master Data/Concordinate.xlsx]
(ooxml, embedded labels, table is Dailysales);

CalendarMaster:   // calendar object join with actual my data (SalesDate) field. you can also replace SalesDate to your data field name.
Load
Date(SalesDate) as SalesDate,
Year(SalesDate) as Year,
'Q' & Ceil(Month(SalesDate) / 3) as Quarter,
Month(SalesDate) as Month,
Day(SalesDate) as Day,
Week(SalesDate) as Week;

Load
Date(MinDate + IterNo()-1) as SalesDate
While(MinDate + IterNo()-1) <=Num(MaxDate);

Load
Min(Date(Date#(SalesDate,'M/D/YYYY'))) as MinDate,
Max(Date(Date#(SalesDate,'M/D/YYYY'))) as MaxDate
Resident DailySales;

ksrinivasan.