Data Check

[sunilvs24]

Hi,

I have a table with some information, based on Months. Now I need a way to find out what is different from each month (which information was added and which was removed).

Eg: My table consists of details of employees with the month. Now i have total of 60 records for June, and for July i will have 63. I want to find out what was that additional 3. It can be a case of 2 employess moving out and 5 getting added or 3 new records getting added with respect to the previous month

Basically, a vlookup kind of a thing to give the details rather than only numbers.

I hope I am clear.

Thanks !!

[v-ljerr-msft]

@sunilvs24

---

I have a table with some information, based on Months. Now I need a way to find out what is different from each month (which information was added and which was removed).

---

According to your description, you can create a measure to get the new added or moved out employees for each month.

Assume you have a table called EmployeeDetails like below:

You can use the formula below to get new added Employees for each month:

New Added Employee = 
IF (
    HASONEVALUE ( EmployeeDetails[Month] ),
    IF (
        DATEADD ( VALUES ( EmployeeDetails[Date] ), -1, MONTH ) <> BLANK (),
        IF (
            CALCULATE (
                COUNTROWS ( VALUES ( EmployeeDetails[EmployeeName] ) ),
                FILTER (
                    VALUES ( EmployeeDetails[EmployeeName] ),
                    EmployeeDetails[EmployeeName]
                        <> CALCULATETABLE (
                            VALUES ( EmployeeDetails[EmployeeName] ),
                            FILTER (
                                ALL ( EmployeeDetails[date] ),
                                EmployeeDetails[Date] = DATEADD ( VALUES ( EmployeeDetails[Date] ), -1, MONTH )
                            ),
                            ALL ( EmployeeDetails[Month] )
                        )
                )
            )
                > 0,
            "Added"
        )
    )
)

And the moved out employees:

Moved Out Employee = 
IF (
    HASONEVALUE ( EmployeeDetails[Month] ),
    IF (
        DATEADD ( VALUES ( EmployeeDetails[Date] ), 1, MONTH ) <> BLANK (),
        IF (
            CALCULATE (
                COUNTROWS ( VALUES ( EmployeeDetails[EmployeeName] ) ),
                FILTER (
                    VALUES ( EmployeeDetails[EmployeeName] ),
                    EmployeeDetails[EmployeeName]
                        <> CALCULATETABLE (
                            VALUES ( EmployeeDetails[EmployeeName] ),
                            FILTER (
                                ALL ( EmployeeDetails[date] ),
                                EmployeeDetails[Date] = DATEADD ( VALUES ( EmployeeDetails[Date] ), 1, MONTH )
                            ),
                            ALL ( EmployeeDetails[Month] )
                        )
                )
            )
                > 0,
            "Moved Out"
        )
    )
)

Then you can use these measures to show the result you want in the report:

Regards

[sunilvs24]

Hi Jerry, Thanks for the piece of code. I tried to use it, but numbers was not as I expected. I guess i have missed something. 

I will re-check on the same again.

Thanks !!

[Anonymous]

@sunilvs24

This is something similar to finding the number of new customers and lost customers between current month and previous month.

The link 

https://javierguillen.wordpress.com/2012/08/24/determining-customer-retention-in-dax/

Check it out and you can get your answer.

If this solves your issue please accept this as a solution and also give Kudos.

Cheers

CheenuSing

[sunilvs24]

Hi, 

The link was helpful, I was able to get the numbers. But only thing which I could not do was to extract the data behind that number.

I need something dynamic, like when i select a particular month i should get the added and moved out data. 

But, really thanks for your reply..It helped me very much.