topic Re: Desktop Compare two excel files in power bi in DAX Commands and Tips

Desktop Compare two excel files in power bi

Anonymous — Thu, 14 Apr 2022 16:53:10 GMT

Hello,

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

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 :

The visual should then show me only : Carter and Alexander

Can you please help ?

Best regards,

Rena.

Re: Desktop Compare two excel files in power bi

amitchandak — Fri, 15 Apr 2022 02:05:47 GMT

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

Re: Desktop Compare two excel files in power bi

tamerj1 — Fri, 15 Apr 2022 07:47:07 GMT

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 ) )

Re: Desktop Compare two excel files in power bi

Icey — Fri, 22 Apr 2022 09:07:05 GMT

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 ) )

Best Regards,

Icey

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

Re: Desktop Compare two excel files in power bi

Anonymous — Mon, 02 May 2022 08:08:25 GMT

Thank you Icey.

It works for me.

Best regards,

Yanne.