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
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.