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

How to use slicer date to compare multiple date columns for correct result

Hi!

 

I have the following sample data below.  When I click a date in the slicer I would like to have the following conditions to bring back the correct results for uncommitted based on the received date:

Batch #Received DateCommitted DateAmount# of Transactions
13/1/20223/1/202250030
23/1/20223/1/202225010
33/1/2022 100015
43/1/20223/3/202220040
53/1/20223/4/202250055
63/2/20223/2/20225020
73/2/20223/2/202230010
83/2/20223/4/202295035
93/2/2022 40045
103/3/2022 120050
113/3/20223/3/202210012
123/3/20223/3/202225

60

 

If I choose the received date of 3/2/2022 my uncommitted results should be the following:

Batch #Received DateCommitted DateAmount# of Transactions
33/1/2022 100015
43/1/20223/3/202220040
53/1/20223/4/202250055
83/2/20223/4/202295035
93/2/2022 40045

 

Based on the following conditions for uncommitted:

  • If received date is <= slicer date and committed date is blank
    OR
  • If received date is <= slicer date and committed date is > slicer date

Thank you for any help on this...much appreciated!

 

KJ

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@kjanse , Date slicer should be independent date table

 

//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate(sum(Table[Amount # ]), Filter(Table, Table[Received Date] <= _max && (isblank(Table[Committed Date]) or Table[Committed Date] >= _max)))

 

 

or check Active employee here

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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

View solution in original post

3 REPLIES 3
kjanse
Frequent Visitor

Thank you both!  @amitchandak I was able to get the results I was looking for with your response.

 

Thank you again!

v-yalanwu-msft
Community Support
Community Support

Hi, @kjanse ;

You could try it. 

1. create a table as slicer.

SLICER = CALENDAR(MIN('Table'[Received Date]),MAX('Table'[Committed Date]))

2.create a flag measure.

Flag = 
var _sli=MAX('SLICER'[Date])
return 
IF(MAX('Table'[Received Date])<=_sli&&(MAX('Table'[Committed Date])>_sli||MAX('Table'[Committed Date])=BLANK()),1,0)

3.Apply it into filter.

vyalanwumsft_0-1648434706345.png

The final output is shown below:

vyalanwumsft_1-1648434732376.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@kjanse , Date slicer should be independent date table

 

//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate(sum(Table[Amount # ]), Filter(Table, Table[Received Date] <= _max && (isblank(Table[Committed Date]) or Table[Committed Date] >= _max)))

 

 

or check Active employee here

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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

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.