cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

Hi @v-nuoc-msft ,

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()).

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

1 ACCEPTED SOLUTION
Helper I

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.

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
6 REPLIES 6
Community Support

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.

Helper I

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

Community Support

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.

Helper I

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

Helper I

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.

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
Super User

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")`

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

DateTable =
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!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors