Dynamic filter on two variable

[froxas]

Hi,

I have situation what I do not know how to solve, maybe somebody could bring me on a way.

I have a table with a contracts: contract number, version, start date and and date.

User needs to get a report which satisfies all below conditions:

1. report should be made on users selected date -> start_date should be before report date

2. report needs to show last version of contract

3. report needs to show only contracts with a status ACTIVE

the main issue is that contract status needs to be calculated like below:

IF report_date (user selected date) is between start_date AND termination_date THEN Status = ACTIVE

It means the status depends on a values of filter. Logically there needs to be double filtering.

How it can be atchieved? maybe through some temporary table?

[v-yuta-msft · Accepted solution]

@froxas ,

I would suggest you create a calculate column using dax below and then you can filter other columns based on that column.

Status =
VAR report_date =
    SELECTEDVALUE ( Calendar[Date] )
RETURN
    IF (
        report_date >= start_date
            && report_date <= termination_date,
        "Active",
        "Inactive"
    )

Community Support Team _ Jimmy Tao

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

[amitchandak]

Refer if this blogpost can help

HR-Analytics-Active-Employee-Hire-and-Termination-trend

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin