Date Query

[lucifer]

How to create measure of black Friday sale only each year(column format : Friday, November 27,2020)

[v-yanjiang-msft]

Hi @lucifer ,

According to your description, I create a sample.

As you can see, Friday, November 27,2020 and Friday, November 26,2021 are blank fridays. Here's my solution.

Create a measure.

Measure =
VAR _T1 =
    CALENDARAUTO ()
VAR _T2 =
    FILTER ( _T1, MONTH ( [Date] ) = 11 && WEEKDAY ( [Date], 2 ) = 4 )
VAR _T3 =
    FILTER (
        ADDCOLUMNS (
            _T2,
            "Rank",
                RANKX (
                    FILTER ( _T2, YEAR ( [Date] ) = YEAR ( EARLIER ( [Date] ) ) ),
                    [Date],
                    ,
                    ASC
                )
        ),
        [Rank] = 4
    )
VAR _Col =
    SELECTCOLUMNS ( _T3, "Blank Friday", [Date] )
RETURN
    MAXX ( FILTER ( 'Table', 'Table'[Date] - 1 IN _Col ), 'Table'[Sales] )

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[lucifer]

@v-yanjiang-msft not working 
i think this solution will not work for multiple sales on same day, your data has one sale on each day 

[v-yanjiang-msft · Accepted solution]

Hi @lucifer ,

I modify the samle data to multiple sales on same day.

And modify the formula:

Measure =
VAR _T1 =
    CALENDARAUTO ()
VAR _T2 =
    FILTER ( _T1, MONTH ( [Date] ) = 11 && WEEKDAY ( [Date], 2 ) = 4 )
VAR _T3 =
    FILTER (
        ADDCOLUMNS (
            _T2,
            "Rank",
                RANKX (
                    FILTER ( _T2, YEAR ( [Date] ) = YEAR ( EARLIER ( [Date] ) ) ),
                    [Date],
                    ,
                    ASC
                )
        ),
        [Rank] = 4
    )
VAR _Col =
    SELECTCOLUMNS ( _T3, "Blank Friday", [Date] )
RETURN
    SUMX ( FILTER ( 'Table', 'Table'[Date] - 1 IN _Col ), 'Table'[Sales] )

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Greg_Deckler]

@lucifer I have a US Holidays table calculation that can flag black friday. United States Holidays - Microsoft Power BI Community