Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
// 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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
18 | |
7 | |
7 | |
5 | |
5 |
User | Count |
---|---|
25 | |
10 | |
10 | |
9 | |
6 |