cancel
Showing results for
Did you mean:
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
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:

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,
)
)
)```

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

Frequent Visitor

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

Community Champion

@sunilvs24

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

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

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

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.

Announcements

#### 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.

#### The Power BI Community Show

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

#### Check it out!

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

#### Charticulator Design Challenge

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

Top Solution Authors
Top Kudoed Authors