Date Picker Selection

[HMUTUAL01]

I am using the date picker in the following query to pick the date in a table. The table only has business dates so I would to modify the selection to pick the next previous date which is available. For example, if the user selects 1/1/24 it would look back and see the closest date prior to 1/1/24 is 12/29/23 and would return that value. Is there a way to implement this?

Sum({<[DS_POST_DT]={"$(=Min(REPORT_DATE_KEY))"}>} DS_Prior_BALANCE)

[Anil_Babu_Samineni]

@HMUTUAL01 If you want prior date from selection, You can try this.

Sum({<[DS_POST_DT]={"$(=Date(Max(DS_POST_DT)-1)"}>} DS_Prior_BALANCE)

[jochem_zw]

See Anil post, this should work, when date  format is ok 

[HMUTUAL01]

@Anil_Babu_Samineni I tried this but nothing was returned. Jochem mentioned "when Date format is ok" so I am not sure if that if that is the issue. The first column is the original without the code and the second is the one where I applied the logic provided. 

[vinieme12]

just specify the date format to match the date format of your field as below

Sum({<[DS_POST_DT]={"$(=Date(Max(DS_POST_DT)-1,'YYYY-MM-DD')"}>} DS_Prior_BALANCE)

[HMUTUAL01]

Below is the error message I receive when I use the logic above. Any ideas why I am getting this error message?

[HMUTUAL01]

How do I set the date picker to only allow the user to pick a date if it exist in the dataset? 

[Anil_Babu_Samineni]

@HMUTUAL01 Please share some sample data set and desired result you need. 

[jochem_zw]

Try this: Sum({<[DS_POST_DT]={"$(=Date((Max(DS_POST_DT)-1),'YYYY-MM-DD')"}>} DS_Prior_BALANCE)