Enabling to filter in time current and past status

[Anonymous]

Spoiler

Dear Community, 

I would like to be able to show data based on their current or previous status depending on how you filter them in time.

To make it more concrete, the data below represent recruiting positions that are open and, at a certain "closed date" are moving to the status "filled".

I would like to be able to make a position appear as "open" if filtering to a date prior to its "closed date" even if it now "Filled" 

Thank you very much in advance!!

Example at the bottom on the list, "Closed date" is Oct 13th 2022, how can I show it as „Open“ if filtering for instance September 2022?

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think "Date Filter" table should be an unrelated date table with your data table.

Dynamic Status =
VAR _MAXDATE =
    MAX ( 'Date Filter'[Date] )
RETURN
    IF (
        MAX ( 'Table'[Date Created] ) <= _MAXDATE,
        IF (
            MAX ( 'Table'[Closed Date] ) = BLANK ()
                || MAX ( 'Table'[Closed Date] ) >= _MAXDATE,
            "Open",
            "Filled"
        ),
        "Filled"
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ,

Thank you very much for your prompt and detailed solution on this. 

Somehow I can't replicate it. I do have a date table in place but it all shows open for me. 

I see though an issue in the suggested approcah. If you look at the example you sent, everything that is blank and after the seleted filtered date shows "filled" even if I would expect it to be "open" with your formula. Any clue on this?

Thank you very much.

SvenDu

[daXtreme]

Not getting any replies since Friday? Have a look: How to Get Your Question Answered Quickly (powerbi.com)