Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Please Help
I have a large dataset with sales information aggregated by date, store#, and most imporantly by hour. See example below;
Date | StoreId | Hour | Online Orders | Phone Orders | Total Orders |
1-Jan | 1 | 11 | 50 | 147 | 197 |
1-Jan | 2 | 10 | 50 | 200 | 250 |
1-Jan | 1 | 13 | 0 | 210 | 210 |
1-Jan | 1 | 12 | 15 | 175 | 190 |
1-Jan | 2 | 13 | 0 | 275 | 275 |
1-Jan | 2 | 11 | 0 | 108 | 108 |
1-Jan | 1 | 10 | 0 | 75 | 75 |
1-Jan | 2 | 14 | 0 | 89 | 89 |
This dataset is not ordered - essentially - I'm trying to create a column flag of T/F to identify when a store has 3 consecutive hours of NO digital sales but Total Orders were over X.
=AND(SUMIFS(OnlineOrders,DATE,[@DATE],StoreId,[@StoreId],Hour,OR([@Hour],[@Hour]+1,[@Hour]-1)) = 0, SUMIFS(TotalOrders,DATE,[@DATE],StoreId,[@StoreId],Hour,OR([@Hour],[@Hour]+1,[@Hour]-1)) > X
Can someone please help?
Solved! Go to Solution.
Here's a pattern for a calculated column. You can adjust the threshold variable as well as the logic for handling cases where three consecutive hours don't exist.
Flag =
VAR vThreshold = 175
VAR vHour = Table1[Hour]
VAR vTotalOrders = Table1[Total Orders]
VAR vOnlineOrdersLast3Hours =
CALCULATE (
SUM ( Table1[Online Orders] ),
ALLEXCEPT ( Table1, Table1[Date], Table1[StoreId] ),
Table1[Hour] >= vHour - 2,
Table1[Hour] <= vHour
)
VAR vResult =
IF ( vOnlineOrdersLast3Hours == 0 && vTotalOrders > vThreshold, "T", "F" )
RETURN
vResult
Proud to be a Super User!
Here's a pattern for a calculated column. You can adjust the threshold variable as well as the logic for handling cases where three consecutive hours don't exist.
Flag =
VAR vThreshold = 175
VAR vHour = Table1[Hour]
VAR vTotalOrders = Table1[Total Orders]
VAR vOnlineOrdersLast3Hours =
CALCULATE (
SUM ( Table1[Online Orders] ),
ALLEXCEPT ( Table1, Table1[Date], Table1[StoreId] ),
Table1[Hour] >= vHour - 2,
Table1[Hour] <= vHour
)
VAR vResult =
IF ( vOnlineOrdersLast3Hours == 0 && vTotalOrders > vThreshold, "T", "F" )
RETURN
vResult
Proud to be a Super User!
I cannot appreciate this enough!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
86 | |
85 | |
68 | |
67 | |
63 |
User | Count |
---|---|
210 | |
118 | |
116 | |
81 | |
74 |