Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lucifer
New Member

Date Query

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

1 ACCEPTED SOLUTION

Hi @lucifer ,

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

vkalyjmsft_0-1665133329674.png

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.

vkalyjmsft_1-1665133388567.png

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.

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

Hi @lucifer ,

According to your description, I create a sample.

vkalyjmsft_0-1665048758230.png

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.

vkalyjmsft_1-1665048851080.png

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.

@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 

Hi @lucifer ,

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

vkalyjmsft_0-1665133329674.png

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.

vkalyjmsft_1-1665133388567.png

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors