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

New Member

## list difference from previous month data

DAXI have a month wise employee data (sample below) and I want to calculate the leavers (removed) and joiners(added) by month.

e.g. Jan 2016, I want to know many entries removed and how many new entries added vs dec 2015, feb 2016 vs jan 2016 and so on in a single query.

Any help would be appreciated.

Regards,

 Year Month Branch Emp Name Emp No 2015 12 B1 Emp1 1 2015 12 B2 Emp2 2 2015 12 B1 Emp3 3 2015 12 B2 Emp4 4 2015 12 B1 Emp5 5 2015 12 B1 Emp6 6 2016 1 B1 Emp1 1 2016 1 B2 Emp2 2 2016 1 B1 Emp3 3 2016 1 B2 Emp4 4 2016 1 B1 Emp7 7 2016 1 B1 Emp6 6 2016 1 B2 Emp8 8 2016 2 B1 Emp1 1 2016 2 B2 Emp2 2 2016 2 B1 Emp3 3 2016 2 B1 Emp7 7 2016 2 B1 Emp6 6 2016 2 B2 Emp8 8 2016 2 B1 Emp9 9 2016 2 B2 Emp10 10 2016 2 B1 Emp11 11 2016 2 B2 Emp12 12 2016 3 B1 Emp1 1 2016 3 B2 Emp2 2 2016 3 B1 Emp3 3 2016 3 B1 Emp7 7 2016 3 B2 Emp10 10 2016 3 B1 Emp11 11 2016 3 B2 Emp12 12 2016 3 B2 Emp13 13 2016 3 B2 Emp14 14 2016 4 B1 Emp1 1 2016 4 B2 Emp2 2 2016 4 B1 Emp3 3 2016 4 B1 Emp7 7 2016 4 B2 Emp10 10 2016 4 B2 Emp14 14
1 ACCEPTED SOLUTION
Microsoft Employee

@babarbashir

In my opinion, you will need some calculcated columns, a calendar table and some measures in this case.

calculated columns

`Date = DATE(Entries[Year],Entries[Month],1)if last month's entry exists = SWITCH (    TRUE (),    CALCULATE (        VALUES ( Entries[Emp No] ),        FILTER (            ALL ( Entries ),            EARLIER ( Entries[Branch] ) = Entries[Branch]                && EARLIER ( Entries[Emp No] ) = Entries[Emp No]                && EARLIER ( Entries[Date] ) = DATEADD ( Entries[Date], +1, MONTH )        )    )        = BLANK (), "N",    "Y")`

Measures

```new entries = CALCULATE(COUNTROWS(Entries),Entries[if last month's entry exists]="N")

removed entires =
COUNTROWS (
FILTER (
ALL ( Entries ),
MAX ( 'Calendar'[Date] ) = DATEADD ( Entries[Date], +1, MONTH )
)
) // last month's entries
- ( COUNTROWS ( Entries ) - [new entries] )```

Check more details in the attached pbix.

1 REPLY 1
Microsoft Employee

@babarbashir

In my opinion, you will need some calculcated columns, a calendar table and some measures in this case.

calculated columns

`Date = DATE(Entries[Year],Entries[Month],1)if last month's entry exists = SWITCH (    TRUE (),    CALCULATE (        VALUES ( Entries[Emp No] ),        FILTER (            ALL ( Entries ),            EARLIER ( Entries[Branch] ) = Entries[Branch]                && EARLIER ( Entries[Emp No] ) = Entries[Emp No]                && EARLIER ( Entries[Date] ) = DATEADD ( Entries[Date], +1, MONTH )        )    )        = BLANK (), "N",    "Y")`

Measures

```new entries = CALCULATE(COUNTROWS(Entries),Entries[if last month's entry exists]="N")

removed entires =
COUNTROWS (
FILTER (
ALL ( Entries ),
MAX ( 'Calendar'[Date] ) = DATEADD ( Entries[Date], +1, MONTH )
)
) // last month's entries
- ( COUNTROWS ( Entries ) - [new entries] )```

Check more details in the attached pbix.

## Helpful resources

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.

#### Join our Community Sticker Challenge

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

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors