Filtering between dates via user defined variable

[Anonymous]

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

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

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.

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

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.

[Anonymous · Accepted solution]

Thank you, very helpful suggestion

[amitchandak]

@Anonymous , 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