Forum Discussion

Seanhu7's avatar
Seanhu7
Frequent Visitor
6 years ago

DAX question for date range

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


3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    // 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's avatar
      Seanhu7
      Frequent Visitor
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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