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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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