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
jgretton
Frequent Visitor

Filter a table to based on date slicers on the same column

I have a table that contains a date, some category fields and a value.  The date is essentially a status or state date. The value represents the state on a particular day.  My goal is to create a matrix view that compares the data for a start date and end date side by side.  I can create the view with a single slicer, but it will allow the users to select more than 2 dates.  I have tried numerous concepts, but none have worked for me.

 

The table structure looks like this:              

AsAtDateCategory1Category2Value
Date1Cat1ACat2XV1
Date1Cat1ACat2yV2
Date1Cat1BCat2XV3
Date2Cat1ACat2XV4
Date2Cat1BCat2XV5
Date2Cat1BCat2YV6
Date2Cat1CCat2YV6

 

I have tried numerous ideas I have found in posts, but none work.  In particular I have tried creating to lists of dates for the slicers and referening them in a measure using code like:

 

IF (asatdate = SELECTEDVALUE(startdateslicer,startdefault) || asatdate = SELECTEDVALUE(enddateslicer,enddefault),1,0)  

 

It never seems to get a value for selected value and always goes to the start and end defaults.  If I leave the default values out I get a 'blank' result.

 

Any ideas are appreciated.

2 REPLIES 2
amitchandak
Super User
Super User

@jgretton , In such cases, it always better to use a date table and try measure like

 

Diff =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = maxx(ALLSELECTED('Date'),'Date'[Date])
return
calculate( sum(Table[value]), filter(Table, Table[Date] =_max)) - calculate( sum(Table[value]), filter(Table, Table[Date] =_min))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

This is a little off of what I am looking for.  I have two independent tables that list the available dates, one for the first date and one for the second.  I want to filter the table such that I can display the values of the two dates side by side.  

 

I have tried various forms of:

 

SelectedRows =

IF(

    SELECTEDVALUE(firstdate[Date]) =  Table[Date]

    || SELECTEDVALUE(seconddate[Date]) =  Table[Date],

    1,

    0

    )

 

If I add a default to the SELECTEDVALUES function calls, the defaults work.  However, the functions never seem to recognize the slected values in the slicers.  I have also tried versions of the code using HASONEVALUE. and I have tried creating a calculated table that is limited to the two dates.  Nothing has worked for me.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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