Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ymirza
Helper II
Helper II

Change in employees (names) from previous month

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

DateUsername
JanuarySam
JanuaryHarvey
JanuaryMaria
JanuaryDean
JanuarySameul
JanuarySam
FebruaryHarvey
FebruarySameul
FebruaryJacob
FebruarySam
MarchWills
MarchJoseph
MarchMaria
MarchHarvey

 

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
NameAction
WillsAdded
JosephAdded
SamRemoved
JacobRemoved
MariaAdded

 

What measure or DAX should I use?

1 ACCEPTED SOLUTION

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

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" )

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

@ymirza

 

Please see the file attached here as well

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?

 

 

Hi @ymirza

 

Could you share your file?

 

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

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

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.