cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sunilvs24
Frequent Visitor

Data Check

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 !!

4 REPLIES 4
v-ljerr-msft
Microsoft
Microsoft

@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:

employee.PNG

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:

result.PNG

 

Regards

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 !!

CheenuSing
Community Champion
Community Champion

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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.

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!