Last working day of the month

Power Query
1

*is there a way or M function the determine the last working day of the month given a date in a column?

example column has
date 1/01/2023 this should return 01/31/2023
but for the month april
date 1/04/2023 this should return 04/28/2023

thanks in advance

Roger

2

Hi @Roger,

There are so many ways to calculate the last weekday but give this CF a go

( Date as date ) as date =>
    [
        EOM = Date.EndOfMonth( Date ),
        n = Date.DayOfWeek(EOM, Day.Friday ),
        LWD = if n <3 then Date.AddDays( EOM, -n) else EOM
    ][LWD]

I hope this is helpful

1 Like
3

Hello @Roger,

Did the response from @Melissa help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark her answer as the SOLUTION.

Thank you

4

thanks for the solution, function can be used in every pbix
great

roger