Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Seanhu7
Frequent Visitor

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 3
Anonymous
Not applicable

 

// 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?

Anonymous
Not applicable

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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.