Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to use a single slicer to filter 2 columns in the same table. Assuming the table below:
Contract Code; Inception Date; Maturity Date
ABC; 2019-10-03;
DEF; 2019-10-05; 2020-05-05;
GHI; 2019-10-03; 2019-10-07
From the above i would like to extract the "active" contracts as at a month-end date.
Assuming from the slicer i choose 2019-10-31 (October Month End). As at end of October ABC and DEF are still active (where Maturity Date is NULL OR Maturity Date > @MonthEnd). So i would like to these 2 as a result.
GHI is no longer active since it matured before end Oct 2019 so this should be excluded.
Your input is appreciated.
Thanks
Solved! Go to Solution.
Hi @kurtazzopardi ,
You can create calendar table , put 'Calendar'[Date] into slicer visual to filter the date, then create measure like DAX below.
Calendar =CALENDARAUTO() .
CumulativeIDs =
var d=SELECTEDVALUE('Calendar'[Date])
return COUNTROWS(FILTER(Table1,Table1[Maturity Date]=BLANK()||(d<=Table1[Maturity Date]&&d>=Table1[Inception Date])))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @kurtazzopardi ,
You can create calendar table , put 'Calendar'[Date] into slicer visual to filter the date, then create measure like DAX below.
Calendar =CALENDARAUTO() .
CumulativeIDs =
var d=SELECTEDVALUE('Calendar'[Date])
return COUNTROWS(FILTER(Table1,Table1[Maturity Date]=BLANK()||(d<=Table1[Maturity Date]&&d>=Table1[Inception Date])))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |