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

[KK_007]

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

ks

[v-nuoc-msft · Accepted solution]

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.

[kkanukurthi]

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

[v-nuoc-msft · Accepted solution]

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.

[kkanukurthi]

Hi @v-nuoc-msft ,

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

[KK_007 · Accepted solution]

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