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.
I have a list of policies with a start date and an end date and i'd like to be able to filter that list to display only policies which are active at a specified date, and as input i want to specify a month.
For example, if the user selects 'August', the policy list filteres all policies which are live in August (inception before August and Expiry after august).
I looked at using a what-if parameter then filters but i can't see a way to use dates.
(using date format day/month/year in example here)
Full Table:
Policy Name | Inception Date | Expiry date
A | 01.01.2020 | 01.04.2021
B | 05.04.2020 | 01.09.2021
C | 01.02.2020 | 01.10.2021
D | 01.09.2020 | 06.12.2021
User defined filter:
'August' (from drop down list or manual typed in search box etc.)
Table Returns:
Policy Name | Inception Date | Expiry date
B | 01.04.2020 | 01.09.2021
C | 01.02.2020 | 01.10.2021
Any advice on the best way to do this? I've looked at what-if parameters to be used as a filter, but it doesn't seem compatible with dates. The user needs to be able to select the month from a drop down/list/manual entry rather than editing the powerbi built in filters.
Many thanks in advance
Solved! Go to Solution.
Hi @SANGST ,
Need to create a separate calendar table, and then create a measure. I did the test:
M =
VAR a =
SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
IF (
a >= MAX ( 'Table'[Inception Date] )
&& a <= MAX ( 'Table'[Expiry date] ),
1,
0
)
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @SANGST ,
Need to create a separate calendar table, and then create a measure. I did the test:
M =
VAR a =
SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
IF (
a >= MAX ( 'Table'[Inception Date] )
&& a <= MAX ( 'Table'[Expiry date] ),
1,
0
)
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, very helpful suggestion
@SANGST , with help from an independent date table, used in slicer
measure =
VAR _max = MAXX(allselected('Date'),'Date' [Date])
return
calculate(countrows(table), filter(Table, Table[Inception date] <_max && [Expiry Date] >=_max))
refer if you need a check for date table
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
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 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |