Sum Aggregated Values With Multiple Filters based on Dynamic Range

[BI_Noob]

Please Help

I have a large dataset with sales information aggregated by date, store#, and most imporantly by hour. See Below

Date _ StoreId _ Hour _ Online Orders _ Phone Orders _ Total Orders

Jan 1 _ 1X _ 10AM _ 50 _ 100 _ 150

Essentially - I'm trying to create a column flag of T/F to identify when a store has 3 consecutive hours of Online sales = 0 but Total Orders were > 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?

[v-chenwuz-msft]

Hi @BI_Noob ,

The same result export by this DAX expression:

Column =
VAR _f =
    FILTER (
        'Table',
        [Date] = EARLIER ( 'Table'[Date] )
            && [StoreId] = EARLIER ( 'Table'[StoreId] )
            && [Hour]
                >= EARLIER ( 'Table'[Hour] ) - 1
            && [Hour]
                <= EARLIER ( 'Table'[Hour] ) + 1
    )
RETURN
    AND ( SUMX ( _f, [Online Orders] ) = 0, SUMX ( _f, [Phone Orders] ) > 0 )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[amitchandak]

@BI_Noob , need a better sample. I think it same as Continuous streak : https://youtu.be/GdMcwvdwr0o

[BI_Noob]

Hi Amit, 

I watched the video and I'm not too sure if it's the same. Attached you'll find a sample dataset. Here's how I got the column to calc what I'm trying to accomplish

=AND(SUM(SUMIFS([Online Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]),SUMIFS([Online Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]+1),SUMIFS([Online Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]-1)) = 0,
SUM(SUMIFS([Phone Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]),SUMIFS([Phone Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]+1),SUMIFS([Phone Orders],[Date],[@Date],[StoreId],[@StoreId],[Hour],[@Hour]-1)) > 0)

[amitchandak]

@BI_Noob , Please provide the same in table format. So that I can try.  Copy paste from excel.

[BI_Noob]

Hi Amit,

Please see below;

Sample Dataset 

Sorry copy and paste from excel causes an error so I had to create link to file. Let me know if this helps explain. I sincerely appreciate your help.