How to calculate Yesterday's Sales using YR_DAYS field from Master Calendar

[mdoll49]

We use a master calendar as shown below. I need to know how to use set analysis to calculate Sum(Sales) for Yesterday's sales using the YR_DAYS field in the below image. Using this field, I'd like to be able to sum Friday, Saturday and Sunday combined as "Yesterday" on a given Monday. Each other day would simply calculate the day prior. Any help would be greatly appreciated!

[vinieme12]

=if(Day(Today()='Mon'

,sum({<INVOIDE_DATE={">=$(=Date(Today()-3,'YYYY-MM-DD 00:00:00'))<=$(=Date(Today()-1,'YYYY-MM-DD 00:00:00'))"}>}Sales)

,

sum({<INVOIDE_DATE={"$(=Date(Today()-1,'YYYY-MM-DD 00:00:00'))"}>}Sales)

)

I would suggest you remove the time part from the INVOICE_DATE field as below

Date(Floor(INVOICE_DATE)) as INVOICE_DATE

[mdoll49]

Thank you for your feedback. While this solution would work in general, our master calendar is designed to handle US holidays. Any way to use the YR_DAYS field in the above table?

[vinieme12]

Assuming. Latest date available is today's date then max(yr_days,2) will return yesterdays date i.e. second max value

If the laste date available in your dataset is yesterday, then simply use max(yr_days) For yesterdays date

Here I've assumed data is available until yesterday

=if(Day(today())='Mon'

,sum({<INVOIDE_DATE={">=max(YR_DAYS,3)<=max(YR_DAYS)"}>}Sales)

,

sum({<INVOIDE_DATE={"=MAX(YR_DAYS)"}>}Sales)

)

[mdoll49]

Unfortunately, the MAX(YR_DAYS) would always yield 254 for the total number of selling days from the calendar. 

Is there a way to do some kind of lookup on the invoice date to pull in the YR_DAYS that correspond?