How to compare values bettween columns? Trouble writing DAX...

[FernandoCandido]

Hy there,

I have a employees table, but not the status, so some employees vanish from the table and others appear...

How can make a ballance between the ins and outs?

I thought to compare the values and take the difference, like this:

month 01 = 001, 002, 003

month 02 = 001, 003, 004

So I have to get the result 2, 1 out and 1 in.

I´m trying to wirte a dax but I´m getting there...gone thriugh the EXCEPT, but get nothing...

Can anybody help?

[Anonymous · Accepted solution]

Thanks for the reply from @Moetazzahran ,please allow me to provide another insight:
HI,@FernandoCandido 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Below are the measure I've created for your needs:

new hire number = 
VAR new1 =
    CALCULATE (
        COUNTROWS ( 'emplopystatus' ),
        FILTER (
            ALLSELECTED ( 'emplopystatus' ),
            'emplopystatus'[Date]
                = CALCULATE (
                    MIN ( 'emplopystatus'[Date] ),
                    ALLEXCEPT ( 'emplopystatus', 'emplopystatus'[id] )
                )
                && 'emplopystatus'[Date] = MAX ( 'emplopystatus'[Date] )
        )
    )
RETURN
    IF ( new1 = BLANK (), 0, new1 )

VAR _previousmonth = CALCULATE(MONTH(MAX('emplopystatus'[Date])),FILTER(ALLSELECTED(emplopystatus),MONTH('emplopystatus'[Date])<_currentMonth))
VAR _previous = 
    CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED('emplopystatus'),'emplopystatus'[id],'emplopystatus'[Date]),MONTH('emplopystatus'[Date])=_previousmonth)
VAR _current = 
    CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED('emplopystatus'),'emplopystatus'[id],'emplopystatus'[Date]),MONTH('emplopystatus'[Date])=_currentMonth)
VAR ex1= COUNTROWS(EXCEPT(SUMMARIZE(_previous,emplopystatus[id]),SUMMARIZE(_current,emplopystatus[id])))
RETURN 
    IF(ex1=BLANK(),0,ex1)

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[FernandoCandido]

Ok, I have monthly tables with the employees Ids.

I made it all concatenated in one table in date order.

The Ids do not have a status as active or inactive, they just desapear from the column as the employee is not active, and surges as a new employee.

then I have this:

ID	Date
001	01/2019
002	01/2019
003	01/2019
001	02/2019
003	02/2019
004	02/2019
001	03/2019
003	03/2019
004	03/2019

This way the 002 wil be an inactive employee in 02/2019 and 004 is a new employee...

I have got the total amount of employee by month, but I need to show how many got inactive and how many are new.

So I have to compare the employee Ids by date (monthly) to return the amount, in this case 2, and mark the 002 as inactive in 02/2019 and 004 as new.

I have got the difference total from last month but it dont show how many got out and how many got in.

The right lower line grafic shows the total employees by month...

It can be solved anyway, creating a new table, column or whatever, DAX or M, I dont know how to write it in DAX...

Thanks again.

The right lower line grafic shows the total employees by month...

It can be solved anyway, creating a new table, column or whatever, DAX or M, I dont know how to write it in DAX...

Thanks again.

[Anonymous · Accepted solution]

Thanks for the reply from @Moetazzahran ,please allow me to provide another insight:
HI,@FernandoCandido 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Below are the measure I've created for your needs:

new hire number = 
VAR new1 =
    CALCULATE (
        COUNTROWS ( 'emplopystatus' ),
        FILTER (
            ALLSELECTED ( 'emplopystatus' ),
            'emplopystatus'[Date]
                = CALCULATE (
                    MIN ( 'emplopystatus'[Date] ),
                    ALLEXCEPT ( 'emplopystatus', 'emplopystatus'[id] )
                )
                && 'emplopystatus'[Date] = MAX ( 'emplopystatus'[Date] )
        )
    )
RETURN
    IF ( new1 = BLANK (), 0, new1 )

VAR _previousmonth = CALCULATE(MONTH(MAX('emplopystatus'[Date])),FILTER(ALLSELECTED(emplopystatus),MONTH('emplopystatus'[Date])<_currentMonth))
VAR _previous = 
    CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED('emplopystatus'),'emplopystatus'[id],'emplopystatus'[Date]),MONTH('emplopystatus'[Date])=_previousmonth)
VAR _current = 
    CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED('emplopystatus'),'emplopystatus'[id],'emplopystatus'[Date]),MONTH('emplopystatus'[Date])=_currentMonth)
VAR ex1= COUNTROWS(EXCEPT(SUMMARIZE(_previous,emplopystatus[id]),SUMMARIZE(_current,emplopystatus[id])))
RETURN 
    IF(ex1=BLANK(),0,ex1)

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[FernandoCandido · Accepted solution]

Almost there!!!

The New Hire Number is correct!! But the other one did not work as well, it is repeating the values periodically, I may did something wrong...

I made a table to check the values:

and your code results:

May the error is because I´m using a calendar table?

Adapting your code I got his:

new hire number = 
VAR new1 =
    CALCULATE (
        COUNTROWS ('fRemunerações' ),
        FILTER (
            ALLSELECTED ( 'fRemunerações' ),
            'fRemunerações'[data]
                = CALCULATE (
                    MIN ( 'fRemunerações'[data]),
                    ALLEXCEPT ( 'fRemunerações', 'fRemunerações'[Matricula] )
                )
                && 'fRemunerações'[data] = MAX ( 'fRemunerações'[data] )
        )
    )
RETURN
    IF ( new1 = BLANK (), 0, new1 )

That one works fine!

The other result is odd:

ex number = 
VAR _currentMonth = MONTH(MAX('fRemunerações'[data]))
VAR _previousMonth = CALCULATE(MONTH(MAX('fRemunerações'[data])),FILTER(ALLSELECTED('fRemunerações'),MONTH('fRemunerações'[data])<_currentMonth))
VAR _previous = 
    CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED('fRemunerações'),'fRemunerações'[Matricula],'fRemunerações'[data]),MONTH('fRemunerações'[data])=_previousMonth)
VAR _current = 
    CALCULATETABLE(SELECTCOLUMNS(ALLSELECTED('fRemunerações'),'fRemunerações'[Matricula],'fRemunerações'[data]),MONTH('fRemunerações'[data])=_currentMonth)
VAR ex1= COUNTROWS(EXCEPT(SUMMARIZE(_previous,'fRemunerações'[Matricula]),SUMMARIZE(_current,'fRemunerações'[Matricula])))
RETURN 
    IF(ex1=BLANK(),0,(ex1))

I´m trying to fix it yet, or use another calculation to get the out employees, since you allready gave me this value.

I will give you the credits for that!! Thanks a lot.

[Anonymous]

Hi,@FernandoCandido 

Have your problems been solved? If you find a solution, feel free to share it with us, which will help other community members with the same problem find a solution faster.

Here's another plan I offer, but it only shows the number of people who will leave this month and next month:

1.Below are the measure I've created for your needs:

ex1 number = 
VAR old1 =
    CALCULATE (
        COUNTROWS ( 'emplopystatus' ),
        FILTER (
            ALLSELECTED ( 'emplopystatus' ),
            'emplopystatus'[Date]
                = CALCULATE (
                    MAX( 'emplopystatus'[Date] ),
                    ALLEXCEPT ( 'emplopystatus', 'emplopystatus'[id] )
                )
                && 'emplopystatus'[Date] = MAX ( 'emplopystatus'[Date] )
        )
    )
RETURN
    IF ( old1 = BLANK (), 0, old1 )

2.Here's my final result, which I hope meets your requirements.

I hope this will help you.

Of course, if you want us to continue to share the cause of the problem, we hope you can share the pbix file with the sensitive data removed. This will help us to further analyze the causes.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[FernandoCandido]

Hey there, saddly it is not working properly. Its does the reght calculation mostly but some time it gets crazy....

But you helped me to achieve the right code:

#NovosT = 
VAR vMesContexto = VALUES(CSV_REM[Matricula])
VAR vMesAntes = CALCULATETABLE(VALUES(CSV_REM[Matricula]),DATEADD('Date'[Data],-1,MONTH))
VAR vResultado = EXCEPT(vMesContexto,vMesAntes)
VAR vTotal = COUNTROWS(vResultado)

RETURN

IF(OR(vTotal = BLANK(),vTotal >799), 0, vTotal)

or this one, both do the right values:

Novos 4 = 
VAR vFuncAtual = VALUES(CSV_REM[Matricula])
VAR vFuncAnterior = CALCULATETABLE(VALUES(CSV_REM[Matricula]),DATESINPERIOD('Date'[Data], MIN('Date'[Data]) -1,-1,MONTH))
VAR vNovos = EXCEPT(vFuncAtual,vFuncAnterior)

RETURN

IF(OR(COUNTROWS(vNovos) = BLANK(),COUNTROWS(vNovos) = [Total de Matriculas]), 0, COUNTROWS(vNovos))

I just dont know why....

And since those codes returns are 1, how can make calculations with that? and get them related with de ID origin to answer what it is and wich is the job of that ID?

Can I do It im M, checking the values and marking the IDs as IN and OUTS.

Im trying to do that with a calculated column but I did not get there yet.... 

The three first columns are the correct values.

I still cant insert the .pbix file or the tables here...why?

Those codes can do the most part correct bu not intirelly:

Novos = 
VAR new1 =
    CALCULATE (
        COUNTROWS('CSV_REM'),
        FILTER (
            ALLSELECTED('CSV_REM'),
                CSV_REM[Data] = CALCULATE (
                    MIN(CSV_REM[Data]),
                    ALLEXCEPT(CSV_REM, CSV_REM[Matricula])
                )
                && CSV_REM[Data] = MIN(CSV_REM[Data])
        )
    )
RETURN
    IF (new1 = BLANK (), 0, new1)

Novos Trad = 
VAR vContexto = VALUES(CSV_REM[Matricula])
VAR vDataContexto = MIN('Date'[Data])
VAR vAntes = CALCULATETABLE(VALUES(CSV_REM[Matricula]),'Date'[Data] < vDataContexto)

VAR vNovos = EXCEPT(vContexto,vAntes)

RETURN

COUNTROWS(vNovos)

Novos Trad = 
VAR vContexto = VALUES(CSV_REM[Matricula])
VAR vDataContexto = MIN('Date'[Data])
VAR vAntes = CALCULATETABLE(VALUES(CSV_REM[Matricula]),'Date'[Data] < vDataContexto)

VAR vNovos = EXCEPT(vContexto,vAntes)

RETURN

COUNTROWS(vNovos)

[FernandoCandido]

Hi,

Thanks a lot for the response! I have got the results turning your fisrt solution upside down, I tought that may not work, but it did well, so this way I got the new hired and the fired ones. I have checked the values and it is correct! Your Ex Number works fine wen you have months selected, I will try this one too.

I have tried to upload the pbix and a table xlsx but it was not allowed... even the zipped files were not allowed....

Thansk again.

[FernandoCandido]

WOW! Thanks!!

The result is exactly what I need!

I will test it right now...

My best regards to you.

[Moetazzahran]

Hello @FernandoCandido ,
Can you please upload a screenshot or dummy data? To showcase your problem as it is a bit unclear what is needed. 

Thank you