Minus 2 days except Weekend

[learner03]

I Have to make a Flagged column based on following condition-

If Arrived date minus 2 days except weekend/holiday has Arrived as NO, then 0
If Arrived date minus 2 days except weekend/holiday has Arrived as Yes, then 1

For identifying weekend/holiday, I have calender table where there is a column that says 1 for workday and 0 for weekend/holiday.

Product
Arrived	Arrived Date	Flagged (1/0)
Yes	24/02/2022
no	12/02/2022
no	13/02/2022
no	22/02/2022
Yes	21/02/2022

[amitchandak]

@learner03 , refer to my blog

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

[learner03]

@amitchandak i saw your pbix and the +10 days is a measure. How can I have this as a calculated column?

[amitchandak · Accepted solution]

@learner03 , try like

new column =
var _rank = maxx(filter('date', 'date'[Date] = table[Arrival date] +2), date[Work Date cont Rank])
return
maxx(filter('date', 'date'[Work Date cont Rank] = _rank), date[date])

[learner03]

@amitchandak I understood the blog, but unable to apply in relation to my query related to minus 2 with arrived date

[amitchandak]

@learner03 , Try a new column in you table, using date table

new column =
var _rank = maxx(filter('date', 'date'[Date] = table[Arrival date] +2), date[Work Date cont Rank])
return
maxx(filter('date', 'date'[Work Date cont Rank] = _rank), date[date])