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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

@ymirza

 

Please see the file attached here as well


Regards
Zubair

Please try my custom visuals

View solution in original post

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

Regards
Zubair

Please try my custom visuals

@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


Regards
Zubair

Please try my custom visuals

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


Regards
Zubair

Please try my custom visuals

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.