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
How to create measure of black Friday sale only each year(column format : Friday, November 27,2020)
Solved! Go to 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.
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.
@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.
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.
@lucifer I have a US Holidays table calculation that can flag black friday. United States Holidays - Microsoft Power BI Community
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
58 | |
27 | |
17 | |
13 |