Change in employees (names) from previous month

[ymirza]

Hi,

I am trying to build a table, which should show us the name of employees which were added or removed from the Previous month.

Below is an example

Assume this is the table  I have. The current one is more than 1000 rows

Date	Username
January	Sam
January	Harvey
January	Maria
January	Dean
January	Sameul
January	Sam
February	Harvey
February	Sameul
February	Jacob
February	Sam
March	Wills
March	Joseph
March	Maria
March	Harvey

In Power BI, I am adding a slicer called Month. So when I select Month from dropdown menu.

Month

March

I should get the following result

Changes from Previous Month
Name	Action
Wills	Added
Joseph	Added
Sam	Removed
Jacob	Removed
Maria	Added

What measure or DAX should I use?

[Zubair_Muhammad]

HI @ymirza

Try this solution

First add a Month Number Column in your Table so that we can identify previous month

Month Number = Switch(Table1[Date],"January",1,"February",2,"March",3)

Next Create a Supporting Calculated Table...From the Modelling Tab>>>New Table

SupportingTable = ALL(Table1[Username])

Now We can use use this MEASURE in your TABLE (say TABLE1)

Measure =
VAR CurrentUsername =
    SELECTEDVALUE ( 'SupportingTable'[Username] )
VAR SelectedMonth =
    SELECTEDVALUE ( Table1[Month Number] )
VAR CurrentMonth =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Username] ),
        FILTER ( Table1, Table1[Username] = CurrentUsername )
    )
VAR Previous_Month =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Username] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Username] = CurrentUsername
                && Table1[Month Number]
                = SelectedMonth - 1
        )
    )
VAR Result =
    IF (
        OR ( Previous_Month > 0, CurrentMonth > 0 ),
        CurrentMonth - Previous_Month
    )
RETURN
    SWITCH ( Result, 1, "Added", -1, "Removed", 0, "No Change" )

[ymirza]

@Zubair_Muhammad you my friend are a genius. I couldnt have thought about this solution myself. Thanks a million for detailed and clearly explained solution.

[Zubair_Muhammad · Accepted solution]

@ymirza

Please see the file attached here as well

[ymirza]

Hi Zubair,

There is slight issue, when I am trying to add another filter "Country" and created a relationship with SupportingTable (UserName) with Table1 (userName) inorder to invoke country location, the REMOVED text under measure is disapperas while Added and 'No change' remains. I tried everything but it it doesnt work. Can you help?

[Zubair_Muhammad]

Hi @ymirza

Could you share your file?

You can upload to onedrive/ googledrive etc and share a link here

[ymirza]

Hi @Zubair_Muhammad

Please check this link. I have uploaded the file here.

https://drive.google.com/file/d/1JQYJ8PL-g-hqMQ0srB9drrtuZrtFkeeI/view?usp=sharing