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

[kjanse]

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 Date	Committed Date	Amount	# of Transactions
1	3/1/2022	3/1/2022	500	30
2	3/1/2022	3/1/2022	250	10
3	3/1/2022		1000	15
4	3/1/2022	3/3/2022	200	40
5	3/1/2022	3/4/2022	500	55
6	3/2/2022	3/2/2022	50	20
7	3/2/2022	3/2/2022	300	10
8	3/2/2022	3/4/2022	950	35
9	3/2/2022		400	45
10	3/3/2022		1200	50
11	3/3/2022	3/3/2022	100	12
12	3/3/2022	3/3/2022	25	60

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

Batch #	Received Date	Committed Date	Amount	# of Transactions
3	3/1/2022		1000	15
4	3/1/2022	3/3/2022	200	40
5	3/1/2022	3/4/2022	500	55
8	3/2/2022	3/4/2022	950	35
9	3/2/2022		400	45

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

[kjanse]

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

Thank you again!

[v-yalanwu-msft]

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.

The final output is shown below:

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 · Accepted solution]

@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...