Previous working day column

[aramirez2]

Hi!

I need to create a previous working day column in my calendar table which has the following structure:

WorkingDay column = "1" means that we opened our store and I need for each Date value which was the previous day we opened the store. However I am not able to reach my goal because:

PrevWorkingDay column works properly from Tuesday to Saturday.

PrevWorkingDay2 column works properly for the last week.

PrevWorkingDay3 only shows up the last working day.

May anyone help me please?

Thanks in advance.

Regards.

[amitchandak]

@aramirez2 , You need few columns in date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)
Work day of month = Sumx(filter(Date, [Month year] = earlier([Month year])),[Work Day])
Work day of week = Sumx(filter(Date, [Weekyear] = earlier([Weekyear])),[Work Day])

measures

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
Last work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))
diff =[This Day] - [Last work day]

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

The measures like

[aramirez2]

Hi @amitchandak

Thanks for your answer. However your formulas does not seem to show the desired result:

I have already created Workday column with "1" and "0" values. This data is correct and PrevWorkDay is nearly to work properly. I would like to fill up Blank PrevWorkDay cells with the very last work day.

[amitchandak]

@aramirez2 , I think, in the blog I shared I have updated nonworking dates with the last working dates 

Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])

[aramirez2 · Accepted solution]

Thanks @amitchandak  but your Work Date Cont column gives today date (Monday) for today record as Previous Work Day. 

I got the solution. Now PrevWorkDay column gives Friday as Previous Work Day for Saturday, Sunday and Monday records.

PreviousWorkDay = 
var a = Hoja1[Date]
RETURN CALCULATE(MAX(Hoja1[Date]); FILTER(ALL(Hoja1); Hoja1[Date] < a && Hoja1[WorkingDay] = 1))