topic Re: Find last Saturday of previous month and last friday of current month end in given Date table in DAX Commands and Tips

Find last Saturday of previous month and last friday of current month end in given Date table

KK_007 — Wed, 29 May 2024 19:58:02 GMT

Hello all,

I have a Date/calender Table. I need two columns. Column1 should show previous month end last Saturday in date format. Column2 should show current month end last Friday date. For example, for row1, if the previous month end last Saturday of April 2024, column1 date should be 27/04/2024 and column2 should show current month end date i.e last Friday date May 31/05/2024.

And for row2 , the column1 date should be immediate +1 of current month last Friday i.e Saturday date 01/06/2024 and column2 should be last Friday of June 28/06/2024. I need to see the difference between these two columns dates. It should repeat though out the date table for all the rows.For above example, row1 difference shows 35 days and row 2 shows difference of 28 days. The required output shown below.

Column1. Column2. Days

Row1. 27/04/2024 31/05/2024 35 days Row2. 01/06/2024 28/06/2024. 28 days

Row3. 29/06/2024. 26/07/2024 28 days

Row4. 28/07/2024. 30/08/2024. 34 dayslease help me with Dax calculated coluns. Appreciate your help in advance.Than

Re: Find last Saturday of previous month and last friday of current month end in given Date table

Anonymous — Thu, 30 May 2024 03:25:27 GMT

Hi KK_007

For your question, here is the method I provided:

Date Table = CALENDAR("4/1/2024", "8/31/2024")

Create columns.

First, you need to get weeks.

weekday = WEEKDAY('Date Table'[Date], 2)

Calculate column 1 from column 2.

Column 2 = CALCULATE( MAX('Date Table'[Date]), FILTER( 'Date Table', MONTH('Date Table'[Date]) = MONTH(EARLIER('Date Table'[Date])) && 'Date Table'[weekday] = 5 ) )

Column 1 = var currntMonth = MONTH(TODAY()) RETURN IF( MONTH('Date Table'[Column 2]) >= currntMonth, CALCULATE( SELECTEDVALUE('Date Table'[Column 2]) + 1, FILTER( ALL('Date Table'), MONTH('Date Table'[Column 2])= MONTH(EARLIER('Date Table'[Column 2])) - 1 ) ), BLANK() )

Craete a viusal.

Regards,

Nono Chen

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

Re: Find last Saturday of previous month and last friday of current month end in given Date table

KK_007 — Mon, 03 Jun 2024 15:22:47 GMT

Thanks for the solution. It works perfect for me. Above solution is for dates between CALENDAR("4/1/2024", "8/31/2024"). I mean it is comparing Month(Today()).

Can you please tell me for dates between 01/01/2023 to 31/12/2025. I need for past and future dates please.

What is the slight modification that I need to do in Dax code?
Thanks

Re: Find last Saturday of previous month and last friday of current month end in given Date table

kkanukurthi — Thu, 06 Jun 2024 08:28:26 GMT

Can i have the same solution for past and future dates please.

Re: Find last Saturday of previous month and last friday of current month end in given Date table

kkanukurthi — Thu, 06 Jun 2024 14:31:21 GMT

Hi Anonymous ,

Thanks for the solution. It works perfect for me. Above solution is for dates between CALENDAR("4/1/2024", "8/31/2024"). I mean it is comparing Month(Today()).

Can you please tell me for dates between 01/01/2023 to 31/12/2025. I need for past year and future year dates please.

What is the slight modification that I need to do in Dax code?
Thanks in Advance