Getting Second latest date from list of Friday dates

[Rabi]

Hi Everyone, 

I am trying to get the second latest Friday date; I tried applying TOP N filter too but it didn't work. Please help me with this. I have a column with a list of Friday Dates.

Regards,

Rabi

[some_bih · Accepted solution]

Hi @Rabi 

If you already have Date / Calendar table, adjust accordingly TOPN and FILTER part.

I created calculated column to check if 'Date'[Date] is expected value as you have.

My timezone is Europe (GMT+) - this is important as today is Friday and your time zone could be different so output could be different. 

Did I answer correctly? Kudos appreciate / accept solution.

SecondLastMonday =

    MINX (

        TOPN (

            2,

            FILTER (

                ADDCOLUMNS (

                    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2023, 12, 31 ) ),

                    "Year", YEAR ( [Date] ),

                    "Month", MONTH ( [DATE] ),

                    --type 2 means first day of week is Monday

                    "DayInWeekName", FORMAT ( [Date], "dddd" )

                ),

                [Date] <= TODAY ()

                    && [DayInWeekName] = "Friday"

            ),

            [Date], DESC

        ),

        [Date]

    )

Cal column

CheckSecLastFriday = IF('Date'[SecondLastMonday]='Date'[Date],TRUE,FALSE)

Ouput

[parry2k · Accepted solution]

@Rabi I just saw this post, although you already have a solution but for last year friday, it need tweaking because the logic will fail on leap year.

SameDayLastYear_Friday =
VAR __LastYear =
DATE(YEAR('Date'[SecondLastFriday])-1,MONTH('Date'[SecondLastFriday]),DAY('Date'[SecondLastFriday]))
VAR __WeekDayOffset = 6 - WEEKDAY ( __LastYear, 1 )
RETURN
__LastYear + __WeekDayOffset

[parry2k · Accepted solution]

@Rabi you can

2nd Last Friday From Today = 
VAR __Today = TODAY () 
VAR __TodayWeekDay = WEEKDAY ( __Today, 1 )
VAR __OffSet = IF ( __TodayWeekDay >= 6, __TodayWeekDay - 6, __TodayWeekDay + 1 ) + 7
RETURN  __Today - __OffSet

also use this expression to get the 2nd last Friday without iterating over date table:

[parry2k · Accepted solution]

@Rabi if you want most recent Friday from today then use this:

2nd Last Friday From Today = 
VAR __Today = TODAY () 
VAR __TodayWeekDay = WEEKDAY ( __Today, 1 )
VAR __OffSet = IF ( __TodayWeekDay >= 6, __TodayWeekDay - 6, __TodayWeekDay + 1 ) 
RETURN  __Today - __OffSet

[parry2k · Accepted solution]

@Rabi you can

2nd Last Friday From Today = 
VAR __Today = TODAY () 
VAR __TodayWeekDay = WEEKDAY ( __Today, 1 )
VAR __OffSet = IF ( __TodayWeekDay >= 6, __TodayWeekDay - 6, __TodayWeekDay + 1 ) + 7
RETURN  __Today - __OffSet

also use this expression to get the 2nd last Friday without iterating over date table:

[parry2k · Accepted solution]

@Rabi I just saw this post, although you already have a solution but for last year friday, it need tweaking because the logic will fail on leap year.

SameDayLastYear_Friday =
VAR __LastYear =
DATE(YEAR('Date'[SecondLastFriday])-1,MONTH('Date'[SecondLastFriday]),DAY('Date'[SecondLastFriday]))
VAR __WeekDayOffset = 6 - WEEKDAY ( __LastYear, 1 )
RETURN
__LastYear + __WeekDayOffset

[Rabi]

@some_bih absolute Genius.

[some_bih]

Hi @Rabi "second latest Friday date" on 15.09.2023 is what date? I am aksing as today is 15.09.2023 and depending when you answer "second latest Friday date" will be another date.

[Rabi]

Hi @some_bih 

Current friday is 15/09/2023 but I want to filter my data with last week's friday date which is 08/09/2023

Thanks !!

[some_bih · Accepted solution]

Hi @Rabi 

If you already have Date / Calendar table, adjust accordingly TOPN and FILTER part.

I created calculated column to check if 'Date'[Date] is expected value as you have.

My timezone is Europe (GMT+) - this is important as today is Friday and your time zone could be different so output could be different. 

Did I answer correctly? Kudos appreciate / accept solution.

SecondLastMonday =

    MINX (

        TOPN (

            2,

            FILTER (

                ADDCOLUMNS (

                    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2023, 12, 31 ) ),

                    "Year", YEAR ( [Date] ),

                    "Month", MONTH ( [DATE] ),

                    --type 2 means first day of week is Monday

                    "DayInWeekName", FORMAT ( [Date], "dddd" )

                ),

                [Date] <= TODAY ()

                    && [DayInWeekName] = "Friday"

            ),

            [Date], DESC

        ),

        [Date]

    )

Cal column

CheckSecLastFriday = IF('Date'[SecondLastMonday]='Date'[Date],TRUE,FALSE)

Ouput

[Rabi]

@parry2k @some_bih 

Hi Guys, 

The DAX above is still giving me date 08/09/2023, but for this week the Friday should be 15/09/2023 as it is the last weeks friday date.

Regards,

Rabi 

[Rabi]

Hi @some_bih 

can you also help me with getting a year date from last week's Friday date for instance, last week Friday date was 08/09/2023 its a year equivalent would be 08/09/2022

thanks for help !!

[some_bih]

Hi @Rabi 

For Friday / Monday option, just adjust part

&& [DayInWeekName] = "Friday" to day you wish, like "Monday"

In my post above name is SecondLastMonday but pat [DayInWeekName] = "Friday"  - typo

For SameDayLastYear_Friday create calculated column (keep in mind that I renamed origanal SecondLastMonday to SecondLastFriday as it was typo)

SameDayLastYear_Friday =

VAR __second_last_Monday_last_year=DATE(YEAR('Date'[SecondLastFriday])-1,MONTH('Date'[SecondLastFriday]),DAY('Date'[SecondLastFriday])+1)

RETURN

__second_last_Monday_last_year