topic Re: EOM Saturday of previous/before month to EOM last Friday of following(Next) in 3years Date table in DAX Commands and Tips

EOM Saturday of previous/before month to EOM last Friday of following(Next) in 3years Date table

kkanukurthi — Fri, 07 Jun 2024 12:36:33 GMT

Hi ThxAlot, Thanks for the solution you provided for my last post. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Find-last-Saturday-of-previous-month-and-last-friday-of/m-p/3977341#M154218

There is a small discrepency in saturday dates in column1.

The date for the Saturday is not coming up in line with Friday date from column2. For example :

Saturday date should start with immidiate to Friday date (i.e Friday date+1), not the every month end saturday.

Below is the image from your output. Al the yellow higlighted are month end saturdays. But it should be +date of Friday end date from column2.

above example expected out :

Saturday date Fridaydate

27/05/2024 31/05/2024

01/06/2024 (Fridaydate+1) 28/06/2024

28/12/2025 31/01/2025

01/02/2025 (Fridaydate+1) 28/02/2025

All Friday dates are perfectly good. Only issue with saturday date. please help me with modified dax code.

Please help me with Dax calculated columns for 3 years (2023 to 2025).

Appreciate your help in advance.Thanks

Re: EOM Saturday of previous/before month to EOM last Friday of following(Next) in 3years Date table

lbendlin — Fri, 07 Jun 2024 23:06:17 GMT

These dates will never change. It is much more efficient to use an external reference table that has all this precomputed.

Re: EOM Saturday of previous/before month to EOM last Friday of following(Next) in 3years Date table

Anonymous — Fri, 14 Jun 2024 06:49:12 GMT

Thank you lbendlin

Hi, kkanukurthi

You can create a new New sat calculated column using the following DAX expression:

New Sat = VAR _value = 'Diff_DAX'[Value]-1 RETURN IF('Diff_DAX'[Value]>=1,MAXX(FILTER('Diff_DAX','Diff_DAX'[Value]=_value),'Diff_DAX'[Fri])+1,'Diff_DAX'[Sat])

This calculated column returns the results you were expecting:

I've uploaded the PBIX file below for this time.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.