cancel
Showing results for
Did you mean:

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

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

 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

What measure or DAX should I use?

1 ACCEPTED SOLUTION
Community Champion

@ymirza

Please see the file attached here as well

Regards
Zubair

6 REPLIES 6
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 SelectedMonth =
SELECTEDVALUE ( Table1[Month Number] )
VAR CurrentMonth =
CALCULATE (
)
VAR Previous_Month =
CALCULATE (
FILTER (
ALL ( Table1 ),
&& 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

Helper II

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

Community Champion

@ymirza

Please see the file attached here as well

Regards
Zubair

Helper II

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?

Community Champion

Hi @ymirza

Regards
Zubair

Helper II

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors