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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Desktop Compare two excel files in power bi

 Hello,

 

Last time i was asking how to display new employees of the current month compared to the previous month in a datasheet

Rena_1-1649955066725.png

in January we had John, Carter, Kim and Alexander.

In February we have John, Carter, Kim, Alexander and Sienna.

The visual should then show me only Sienna

 

This is the solution that worked for me : 

 

Flag = 
var _preMonth=DATEADD('Table'[Month],-1,MONTH)
var _preEmps= SUMMARIZE(FILTER(ALL('Table'),YEAR([Month])= YEAR(_preMonth) && MONTH([Month])=MONTH(_preMonth)) ,[Name of Employees])
return IF(_preMonth<>BLANK(),IF( MAX('Table'[Name of Employees]) in _preEmps,0,1))

 

 

Now i would like to have the list of all the employees who have disappeared (they are no longer in the database) 

 

Example : 

Rena_0-1649954927754.png

The visual should then show me only : Carter and Alexander

 

Can you please help ?

Best regards,

 

Rena.

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Anonymous ,

 

You can just change your expression like so:

Flag 2 = 
VAR _curMonth =
    DATEADD ( 'Table2'[Month], 1, MONTH )
VAR _curEmps =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table2' ),
            YEAR ( [Month] ) = YEAR ( _curMonth )
                && MONTH ( [Month] ) = MONTH ( _curMonth )
        ),
        [Name of Employees]
    )
RETURN
    IF (
        _curMonth <> BLANK (),
        IF ( MAX ( 'Table2'[Name of Employees] ) IN _curEmps, 0, 1 )
    )

Icey_0-1650618339057.png

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @Anonymous ,

 

You can just change your expression like so:

Flag 2 = 
VAR _curMonth =
    DATEADD ( 'Table2'[Month], 1, MONTH )
VAR _curEmps =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table2' ),
            YEAR ( [Month] ) = YEAR ( _curMonth )
                && MONTH ( [Month] ) = MONTH ( _curMonth )
        ),
        [Name of Employees]
    )
RETURN
    IF (
        _curMonth <> BLANK (),
        IF ( MAX ( 'Table2'[Name of Employees] ) IN _curEmps, 0, 1 )
    )

Icey_0-1650618339057.png

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thank you Icey.

It works for me.

Best regards,

Yanne.

amitchandak
Super User
Super User

@Anonymous , Create a date using month year. Using common date and employee table follow this customer retention logic

 

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

Hi @Anonymous 
I hope "Month" is a date column. Then you can drag this measure into the filter pane and select "Not is blank"

Flag =
VAR NumberOfMonths = 1
VFlag =
VAR NumberOfMonths = 1
VAR CrrentPeriod =
    MAX ( 'Table'[Month] )
VAR PreviousPeriod =
    DATEADD ( CrrentPeriod, - NumberOfMonths, MONTH )
VAR LastPeriodEmployees =
    CALCULATETABLE (
        VALUES ( 'Table'[Name of Employees] ),
        'Table'[Month] = PreviousPeriod
    )
VAR CurrentPeriodEmployees =
    CALCULATETABLE (
        VALUES ( 'Table'[Name of Employees] ),
        'Table'[Month] = CrrentPeriod
    )
RETURN
    COUNTROWS ( EXCEPT ( LastPeriodEmployees, CurrentPeriodEmployees ) )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors