DAX question for date range

[Seanhu7]

Hi, looking for DAX query on date range where sales would = zero for a date range selected using a slicer.

My dataset id 6 months + worht of date for sales by store by day by products and looking to show where accum sales = zero for a date range.

So on below example if slicer is 03/01 to 05/01 then accum sales = zero and therefore the store would return in this list.

Thanks in advance

Sean

Retailer Store Number Retailer Store Name First Date Export Sales Value

4126 HESSLE 02/01/2020 £161.98
4126 HESSLE 03/01/2020 £0.00
4126 HESSLE 04/01/2020 £0.00
4126 HESSLE 05/01/2020 £0.00
4126 HESSLE 06/01/2020 £143.24
4126 HESSLE 07/01/2020 £99.68
4126 HESSLE 08/01/2020 £0.00
4126 HESSLE 09/01/2020 £0.00
4126 HESSLE 10/01/2020 £0.00
4126 HESSLE 11/01/2020 £0.00
4126 HESSLE 12/01/2020 £142.40

[Anonymous]

// Measures can't return tables.
// We can return a bool flag for a store, though,
// that will tell you whether the store
// had 0 sales in the selected period of
// time or not. This way you'll be able
// to filter stores in a visual.

[Total Sales] = SUM( Sales[Amount] )

[Store Had 0 Sales] =
var __oneStoreVisible = HASONEVALUE( Stores[StoreId] )
var __sales = [Total Sales]
return
	if( __oneStoreVisible, __sales = 0 )
	
// Bear in mind that Sales should be
// your fact table, all its columns
// should be hidden and all slicing
// should be done through dimensions.
// Stores is a dimension. Sales is
// a fact table.

Best

D

[Seanhu7]

Thanks - I have used this by=ut returning a true/false that I am unable to use in a slicer?

[Anonymous]

You don't use it in a slicer. It's a measure. You put your stores in a visual, put this measure in it and then filter the column in the visual (you display only the rows with TRUE in them). As I said, measures can't return tables and can't be used in slicers but visuals can filter their own columns, and hence by values of measures. You can also have a visual level filter that only displays the rows where the measure is TRUE.

Best
D