Need a more efficient way of getting last working day of the month

[AlvinLy]

Hello,

I have a DAX code of getting last working day of the month that works and I'm trying to find a better way of writing the Power Query version of it. 

I am trying to create a company calendar table. I have a list non-weekend dates that are company and vacation days off. From this I created the following in Power query and DAX:

Date	Year	Month	Year Month	IsWorkingDay
2024-01-04	2024	01	2024-01	Y
2024-01-05	2024	01	2024-01	N
...	...	...	...	...
2024-03-29	2024	03	2024-03	N

So for January it was a normal month so last working day would be Jan 31. In March we had a vacation so last working day was Mar 28 (29 is vacation, 30 and 31 is Sat and Sun)

For DAX I got the last working day of the month by using the following code:

Month End Date = 
VAR _Year = Calendar[Year]
VAR _Month = Calendar[Month Number]
RETURN
FORMAT(
MAXX(
    FILTER(Date, 
    Calendar[Year] = _Year &&
    Calendar[Month Number] = _Month &&
    Calendar[Bantrelworkday] = "Y"),
    Calendar[Date]
), "dddddd")

I'm open to suggestions for making the above more efficient. For Power Query I used the following code:

Table.AddColumn(#"Previous Step", "EOM Date", each
    let 
    currentYearMonth = Text.From(Date.Year([Date])) & "-" & Text.PadStart(Text.From(Date.Month([Date])),2,"0"),
    filteredTable = Table.SelectRows(#"Previous Step", each [Year Month] = currentYearMonth and [BantrelWorkday] = "Y"),
    maxDate = List.Max(filteredTable[Date])
    in maxDate)

Now for the Power Query i get the same output, but it takes forever to load, so i'm thinking there's a much better way of doing this, but i'm not as familiar with Power Query

[Anonymous · Accepted solution]

Hi @AlvinLy ,

Try grouping by year and month to find out the last working day and then merge the queries:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZRBisMwFEPvknUh/pITJ5fofii9/zWGgSY1erN98JEQz369llr7qqa+PJaf5f34A1uC/QLPDxgJjjw5E1QDKRCBOJOqg6Bv7SADBJULndUySwWCzjIIJhY6C52FlXWAoLOxs2NnrQBKELNr7Qm2PNkTjARHghPFWsZUgaBsGaSDoG9KokmSO+sAQWc1EO6LzimJJkmurK8kN0FnYWVh5pTEKYC/Ajw/wAl6nmwJ9gQjwZHgzJQUwPglDAEMAQwBDAE8CXBnDRBUTgEMAQwBDAE8CXBlpQDGL2EIYAhgCOB/BDB29vzQ3r8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, BantrelWorkday = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    AddYearMonthColumn = Table.AddColumn(#"Changed Type", "YearMonth", each Date.ToText([Date], [Format="yyyy-MM"])),
    FilterWorkingDays = Table.SelectRows(AddYearMonthColumn, each [BantrelWorkday] = "Y"),
    GroupByYearMonth = Table.Group(FilterWorkingDays, {"YearMonth"}, {{"LastWorkingDayOfMonth", each List.Max(_[Date]), type date}}),
    #"Merged Queries" = Table.NestedJoin(AddYearMonthColumn, {"YearMonth"}, GroupByYearMonth, {"YearMonth"}, "GroupByYearMonth", JoinKind.LeftOuter),
    #"Expanded GroupByYearMonth" = Table.ExpandTableColumn(#"Merged Queries", "GroupByYearMonth", {"LastWorkingDayOfMonth"}, {"LastWorkingDayOfMonth"})
in
    #"Expanded GroupByYearMonth"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum 

[ThxAlot]

[lbendlin]

There is no need for any of this.  Calendar data is immutable.  Create an external reference table that has this precomputed once. Re-use everywhere.

[AlvinLy]

Hi Ibendlin,

Very good point! I was thinking of that too, but after discussing with my boss, he requested this as we have clients with different calendars (varying company days off, period end date, etc.) and they would send us a list of their days off and wants us to create reports based on those. He wanted a robust way of doing it. I found a solution, as shown above, but it felt sloppy and bad technique so I was wondering if there were any better ideas. 

[lbendlin]

If you must: NETWORKDAYS function (DAX) - DAX | Microsoft Learn