I want to create 2 virtual tables using Date Filters and compare the changes in 2 tables

[vrushabjain510]

Hi All,

I have data with 6 columns like Date, Name, Email ID, Job Title, Department and Location.

I want to have 2 Date filters which will extract tables based on the two date filters.

Example show in Images below

So I need to compare these two tables and want output in a list like

People who joined the company:

1) Sarath Kumar

People who left the company:

1) Anand Rajput

People whose Job title changed:

1) Bijoy Jha

2) Dinesh Singh

3) Raj Bose

Is this possible to handle such a situation in DAX?

Please help

Thanks,

Vrushab 

[v-eqin-msft · Accepted solution]

Hi @vrushabjain510 ,

Please follow these steps:

1. Add a Month table(for example):

Month =
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( "2020/1/1", "2020/12/1" ),
        "MonthNumber", MONTH ( [Date] ),
        "Month", FORMAT ( [Date], "MMMM" )
    )
)

2. Add a Name table:

Name =
DISTINCT ( 'Table'[Name] )

3. Create measures based on selected two months:

first =
CALCULATE (
    MAX ( 'Table'[Job Title] ),
    FILTER (
        'Table',
        MONTH ( [Date] ) = MIN ( 'Month'[MonthNumber] )
            && 'Table'[Name] = MAX ( 'Name'[Name] )
    )
)

second =
CALCULATE (
    MAX ( 'Table'[Job Title] ),
    FILTER (
        'Table',
        MONTH ( [Date] ) = MAX ( 'Month'[MonthNumber] )
            && 'Table'[Name] = MAX ( 'Name'[Name] )
    )
)

4. Compare [first] and [second]

Measure =
IF (
    [first] = BLANK ()
        && [second] <> BLANK (),
    " Joined",
    IF (
        [first] <> BLANK ()
            && [second] = BLANK (),
        "Left",
        IF (
            [first] <> BLANK ()
                && [second] <> BLANK ()
                && [first] <> [second],
            "Job title changed"
        )
    )
)

The final output is shown below:

Please take a look at the pbix file here.

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

[vrushabjain510]

@v-eqin-msftThis works perfectly. 
Thank you so much!

[v-eqin-msft]

Hi @vrushabjain510 

Glad you found it helpful.😀

Best Regards,
Eyelyn Qin

[vrushabjain510]

Hi Amit @amitchandak , Thanks for the response. I have only one Date column. I have duplicated the same date filter and one date filter interacts with 1 table only (interaction with other table is off). 

My task is to compare the two produced table and find out who left the company, who joined the company and who changed their job title.

I hope you understood my requirement.

Thanks,

Vrushab Jain

[amitchandak]

@vrushabjain510 , how do we know the join date, termination date, or change department date? If you have two/ three dates, refer to his solution

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Now the same date with two different filters on two tables. Use interactions. First filter should only interact with first table and second should interact with 2 tables

https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

or have two date tables

example -https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601