Obtain last workday in new column (M)

[Anonymous]

Hi, 

I need to obtain the difference of a value between the row's date and the previous working day, for each project: 

PROJECT - DATE - VALUE (on date)- Difference (of Value) from the last working date

I assume that the first step is obtaining the table: 

DATE - PREVIOUS WORKING DATE to merge with the table with values. 

I have managed to get it in DAX, as an added column in Power BI, but I need to convert it into M to add the column to the query. The sintax I have used and which works  is: 

Previous working day= 

MAXX( 

    FILTER (Dates, Dates[Dateslist] < EARLIER(Dates[Dateslist])

     && WEEKDAY(Dates[Dateslist];3) <5);

     DATES[Dateslist]) 

Any inspiration on the best way to do this is more than welcome! 

Thanks! 
María 

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

Try these 2 custom Columns

First add a column for weekday

Date.DayOfWeek([Dateslist],1)+1

Now a column for PreviousWorking Day

let mydate=[Dateslist] in

List.Max(
Table.SelectRows(AddedCustom,each [Dateslist] < mydate and [DayofWeek] <6)[Dateslist]
         )

AddedCustom above is reference to previous step

[Zubair_Muhammad]

@Anonymous

Please see attached excel file's Query Editor for above steps