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.
dear all,
I have an ID that has an starting date and ending date. I would like to have a year filter on my report which filters all the values that are between (or equal <= and >=).
How can i do this?
best
Solved! Go to Solution.
HI @Anonymous ,
I'd like to suggest you to add a calendar table(no relationship to current table) as source of slicer and write a measure formula to filter and concatenate and display all matched years.
Measure = VAR selected = MAX ( 'calendar'[Date] ) VAR _calendar = CALCULATETABLE ( CALENDAR ( MIN ( 'Table'[Start] ), IF ( MAX ( 'Table'[End] ) <> BLANK (), MAX ( 'Table'[End] ), MAXX ( ALLSELECTED ( 'Table'[End] ), [End] ) ) ), VALUES ( 'Table'[ID] ) ) RETURN CONCATENATEX ( DISTINCT ( SELECTCOLUMNS ( FILTER ( _calendar, [Date] >= selected ), "Year", YEAR ( [Date] ) ) ), [Year], "," )
Regards,
Xiaoxin Sheng
To further extend my explanation, i hereby have an example of what my data looks like:
ID | Start | End |
A | 1-1-2015 | 31-12-2018 |
B | 1-6-2017 | null |
C | 31-7-2019 | 8-2-2022 |
and how i would like to show it:
So in a year filter with all the years (or relevant years) from my datetable:
ID | To appear in year filter |
A | 2015, 2016, 2017, 2018 |
B | 2017… (and all years in datetable) |
C | 2019, 2020, 2021, 2022 |
HI @Anonymous ,
I'd like to suggest you to add a calendar table(no relationship to current table) as source of slicer and write a measure formula to filter and concatenate and display all matched years.
Measure = VAR selected = MAX ( 'calendar'[Date] ) VAR _calendar = CALCULATETABLE ( CALENDAR ( MIN ( 'Table'[Start] ), IF ( MAX ( 'Table'[End] ) <> BLANK (), MAX ( 'Table'[End] ), MAXX ( ALLSELECTED ( 'Table'[End] ), [End] ) ) ), VALUES ( 'Table'[ID] ) ) RETURN CONCATENATEX ( DISTINCT ( SELECTCOLUMNS ( FILTER ( _calendar, [Date] >= selected ), "Year", YEAR ( [Date] ) ) ), [Year], "," )
Regards,
Xiaoxin Sheng
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |