Forum Discussion
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...
// 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
Thanks - I have used this by=ut returning a true/false that I am unable to use in a slicer?
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