Custom Column Today and Yesterday

[marcio_fornari]

Hello Everyone, i need help to add custom column into my calendar whit Power Query

I need a column write "Today" and "Yesterday".. but there are some conditions for this.

It's very complex for me and It's very confused 😅
I will try explain:

Has 2 situations: 
Holiday Date and Sunday (workday = 0) 
Mon,Tue,Wed,Thu,Fri,Sat (workday = 1)

IF Yesterday date, (today is Tuesday, yesterday was monday)
and the yesterday workday is 1 then write "Yesterday" at Today Date.
Example:

Today is 2021-08-23..

For me the date of tomorrow 2021-08-24 it's considered Today date and Today date it's considered Yesterday.. but if have holiday or sunday or Today is Friday or Monday it's changed.

Example below for Today:
if today is Friday (2021-08-27), for me i consider Today as (saturday and Monday)

Example below for Yesterday:

if Today is monday  i consider Yesterday date as the Today Date and the Last Workday = 1 from today
Example.. Today is monday. For me, yesterday is Today (monday) and Saturday

With a holiday between the dates:
Write Today:
if the next day from today the workday is 0, i considered Today as Today Date + next day where workday is 1
Example.. today is 2021-08-23 and tomorrow the workday is 0..

Write Yesterday:
if Yesterday the workday was 0, i considered Yesterday as the last workday = 1 from today
Example:
if today is 2021-08-24 and yesterday (2021-08-23) was holiday(work day = 0)
Yesterday for me is Monday and Today
and Tomorrow 2021-08-25 is Today.

That's it..
Very confused..

[v-jingzhang]

Hi @marcio_fornari 

Sorry I'm still very confused about the logic. Can you help answer the following questions first?

When you say "today is ...", does this "today" refer to the local date (system date) when you refresh the query?

In general, my understanding is that if "today" is a working day, its previous working day should always have "Yesterday" and its next working day should always have "Today". But in some of above examples Today has "Yesterday" (images 1, 3,5), and in image 4 it has "Today" while in image 2 it has null?

Additionally, all of your examples have an assumption that today is a working day. What if today is not a working day?

And in all examples, all holidays have only a single day. Will some holidays have several continuous days in the calendar? If so, what should be the result?

Can you help us understand the logic more clearly?

Regards,
Community Support Team _ Jing