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

[kkanukurthi]

Hi @v-nuoc-msft , 

Thanks for the solution for my latest post .https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Find-last-Saturday-of-previous-month...

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 help me with Dax code 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 in Advance

[v-jialongy-msft]

Hi @kkanukurthi 

I have made some small changes to the dax:

Date = CALENDAR(DATE(2023,1,1),DATE(2025,12,31))

Weekday = WEEKDAY('Date'[Date], 2)

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

Column 1 = IF(MONTH('Date'[Date])=1,BLANK(),
    CALCULATE(
        SELECTEDVALUE('Date'[Column 2]) + 1,
        FILTER(
            ALL('Date'),
            MONTH('Date'[Column 2])= MONTH(EARLIER('Date'[Column 2])) - 1&&YEAR('Date'[Date])=
        YEAR(EARLIER('Date'[Date]))
        )
    )
)

Result:

Best Regards,

Jayleny

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

[kkanukurthi]

Hi @v-jialongy-msft ,

Thankyou very much for the soulution provided. It was very near to the expected result. but December Saturday dates are missing for all 3 years as shown below. Please check the dax code and help me.

https://community.fabric.microsoft.com/t5/Desktop/Find-last-Saturday-of-previous-month-and-last-frid...

 Thankyou

[v-jialongy-msft]

Hi @kkanukurthi 

I have modified the DAX of column1:

Column 1 = IF(MONTH('Date'[Date])=1,CALCULATE(
    MAX('Date'[Date]), 
    FILTER(
        'Date', 
        YEAR('Date'[Date])=
        YEAR(EARLIER('Date'[Date]))-1
        &&
        MONTH('Date'[Date]) = 12 
        && 
        'Date'[weekday] = 6
    )
),
    CALCULATE(
        SELECTEDVALUE('Date'[Column 2]) + 1,
        FILTER(
            ALL('Date'),
            MONTH('Date'[Column 2])= MONTH(EARLIER('Date'[Column 2])) - 1&&YEAR('Date'[Date])=
        YEAR(EARLIER('Date'[Date]))
        )
    )
)

Result:

Best Regards,

Jayleny

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

[kkanukurthi]

Thankyou @v-jialongy-msft. It is perfect now 

[kkanukurthi · Accepted solution]

Hi @rajendraongole1 

I have already tried with date table          ( 01/01/2023, 12/31/2025) replacing ("4/1/2024", "8/31/2024") , but it didnt work. Please check the below Dax code in link below.

Hi @rajendraongle 1, 

I have already tried with date table                   ( 01/01/2023, 12/31/2025) replacing ("4/1/2024", "8/31/2024") , but it didn't work. Please check the Dax code below.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Find-last-Saturday-of-previous-month...

It works for current year 2024 only. Please use above Dax and change the code please. It would be helpful if you can provide me pbix file.

Thanks

It works for current year 2024 only. Please use above Dax and change the code please. It would be helpful if you can provide me pbix file.

Thanks

[rajendraongole1]

Hi @kkanukurthi - If you need to adjust for past and future dates, make sure the DateTable covers all the necessary dates, and the calculations will automatically adapt based on the current date.

In above solution thread as observed Date Table is created for some period

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

As per your question, "Can you please help me with Dax code for dates between 01/01/2023 to 31/12/2025."

Create a calculated Table for Data and mark it as Date in your model . 

DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2023, 1, 1), DATE(2025, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"Weekday", WEEKDAY([Date], 2) -- 2 makes Monday = 1, Sunday = 7
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!