Calculated column condition with date from slicer

[Krzysztof_Hyla]

Goal:

I want to add a calculated column to my table (if statement) based on the date slicer selection.

This will then allow me to filter the table and show only the positions that were active on a particular date in history.

Current State:

I have a 'Positions' table with the history of employee positions (job titles) in our company.

It has a [Start] and [End] date column for each position (there are multiple entries per employee)

The [End] dates for current positions are empty.

The 'Calendar' table is disconnected from the 'Positions' table.

The date slicer type is 'Before'

The additional slicer 'Show Row in Report?' is coming from the new column and the intention is to keep the '1' value selected.

Problem:

My formula seems to ignore the slicer selection.

Only the ISBLANK(Positions[End]) conditions seems to work.

How can I make it work?

Show Row in report? =

VAR SelectedDate = max('Calendar'[Date])

RETURN

IF(

AND (Positions[Start]<=SelectedDate, OR (Positions[End] > SelectedDate, ISBLANK(Positions[End]) ) )

,1,0)

[nandukrishnavs · Accepted solution]

@Krzysztof_Hyla 

Create the below measure.

Show Row in report? =
VAR SelectedDate =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        AND (
            SELECTEDVALUE ( 'Positions'[Start] ) <= SelectedDate,
            OR (
                SELECTEDVALUE ( 'Positions'[End] ) > SelectedDate,
                ISBLANK ( Positions[End] )
            )
        ),
        1,
        0
    )

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Krzysztof_Hyla]

Thank you! This has helped me a lot.

I've looked around and I understand now that I cannot directly control column calculation based on slicer value