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!
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:
Thank you for any help on this...much appreciated!
KJ
Solved! Go to 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
Thank you both! @amitchandak I was able to get the results I was looking for with your response.
Thank you again!
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.
@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
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |