cancel
Showing results 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

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

#### 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 Monthly Update - June 2024

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

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

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

Top Solution Authors
Top Kudoed Authors