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

Hi @kkanukurthi ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new column to find the last Friday current month.

``````last friday current month =
CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
MONTH('Table'[Date]) =
MONTH(EARLIER('Table'[Date]))
&& YEAR('Table'[Date]) = YEAR(EARLIER('Table'[Date])) &&
'Table'[weekday] = 5
)
)``````

3.Create the new column to find the last Saturday previous month.

``````last saturday previous month =
var last_mon = 'Table'[Date]
var last_year = CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
MONTH('Table'[Date]) =
MONTH(last_mon) - 1
&& YEAR('Table'[Date]) = YEAR(last_mon) &&
'Table'[weekday] = 6
)
)
var last_12 =
CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
MONTH('Table'[Date]) = 12
&& YEAR('Table'[Date]) = YEAR(last_mon) - 1 &&
'Table'[weekday] = 6
)
)
RETURN
IF(MONTH('Table'[Date]) = 1, last_12, last_year)
``````

4.The result is shown below.

Best Regards,

Wisdom Wu

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

Community Support

Hi @kkanukurthi ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new column to find the last Friday current month.

``````last friday current month =
CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
MONTH('Table'[Date]) =
MONTH(EARLIER('Table'[Date]))
&& YEAR('Table'[Date]) = YEAR(EARLIER('Table'[Date])) &&
'Table'[weekday] = 5
)
)``````

3.Create the new column to find the last Saturday previous month.

``````last saturday previous month =
var last_mon = 'Table'[Date]
var last_year = CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
MONTH('Table'[Date]) =
MONTH(last_mon) - 1
&& YEAR('Table'[Date]) = YEAR(last_mon) &&
'Table'[weekday] = 6
)
)
var last_12 =
CALCULATE(
MAX('Table'[Date]),
FILTER(
'Table',
MONTH('Table'[Date]) = 12
&& YEAR('Table'[Date]) = YEAR(last_mon) - 1 &&
'Table'[weekday] = 6
)
)
RETURN
IF(MONTH('Table'[Date]) = 1, last_12, last_year)
``````

4.The result is shown below.

Best Regards,

Wisdom Wu

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