Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Obtain last workday in new column (M)

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 

 

 

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

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


Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

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


Regards
Zubair

Please try my custom visuals

@Anonymous

 

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

 

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.