DAX - EXCEPT not returning correct results

[ampams1]

I have a file that contains information about a certain process. Each month I get a file of the processes filed in the previous month. These files are all pulled into a master table. Let's call it Master Process Data. I've attached a sample table of the Master Process Data below. It has many other columns but these are the only ones that I care about.

Source Date	ID	Process Creation date
1/11/2022	ABC.2022.123	1/2/2018
1/11/2022	ABC.2022.124	2/1/2019
1/11/2022	ABC.2022.125	4/5/2022
1/11/2022	ABC.2022.126	6/9/2018
1/11/2022	ABC.2022.127	2/1/2019
1/12/2022	ABC.2022.123	1/2/2018
1/12/2022	ABC.2022.124	2/1/2019
1/12/2022	ABC.2022.125	4/5/2022
1/12/2022	ABC.2022.127	2/1/2019
1/12/2022	ABC.2022.132	2/12/2022

You may have noticed that each month certain IDs carry over, some are new, and some do not carry over into the next month. I like to keep track of the ones that do not carry over from one month to the next. I used to this manually in power query by creating 3 tables, one for the current month (Table A) and two for the previous month (Table B and C). I would perform a left outer join getting all the rows from Table A that matched Table B. Let's call this table D. This would get me IDs that were created in the current month appended on and the ones that carried over from month to month. I would then perform a left anti join between Table C and Table D only getting the rows from Table C (i.e. the IDs that do not carry over from the previous month into the next). Let's call this Table E. I've illustrated this process below.

Table A: Current Month Data

ID	Process Creation date
ABC.2022.123	1/2/2018
ABC.2022.124	2/1/2019
ABC.2022.125	4/5/2022
ABC.2022.127	2/1/2019
ABC.2022.132	2/12/2022

Table B and C: Previous Month Data

ID	Process Creation date
ABC.2022.123	1/2/2018
ABC.2022.124	2/1/2019
ABC.2022.125	4/5/2022
ABC.2022.126	6/9/2018
ABC.2022.127	2/1/2019

Table D : Left Outer Join between A and B

ID	Process Creation date	ID join	Process Creation Date join
ABC.2022.123	1/2/2018	ABC.2022.123	1/2/2018
ABC.2022.124	2/1/2019	ABC.2022.124	2/1/2019
ABC.2022.125	4/5/2022	ABC.2022.125	4/5/2022
ABC.2022.127	2/1/2019	ABC.2022.127	2/1/2019
ABC.2022.132	2/12/2022	0	null

Table E: Left anti join between Table C and D

ID	Process Creation date
ABC.2022.126	6/9/2018

I've been trying to automate this process in PowerBI but it is not giving the results I want. Here is the code I've been using to replicate the Power Query Process.

Table = 

VAR _TableA = SELECTCOLUMNS(FILTER('Master Process Data','Master Process Data'[SourceDate]=DATE(2022,12,1)),"ID",'Master Process Data','Master Process Data'[ID])

VAR _TableB = SELECTCOLUMNS(FILTER('Master Process Data','Master Process Data'[SourceDate]=DATE(2022,11,1)),"ID",'Master Process Data','Master Process Data'[ID])

VAR _TableC = SELECTCOLUMNS(FILTER('Master Process Data','Master Process Data'[SourceDate]=DATE(2022,11,1)),"ID",'Master Process Data','Master Process Data'[ID])
VAR _TableD = NATURALLEFTOUTERJOIN(_TableA,_TableB)

VAR _TableE = EXCEPT(_TableC,_TableD)
return
_TableE

Instead of the power query result, I get a blank table. 

ID

I've tried to do the same operations with the whole dataset, just the ID columns, the ID columns and creation date columns but the result remains the same. I've tried many different other combinations of join operations and none of them get me the data that I want. What could be going wrong with the EXCEPT? I found another member who was facing a similar issue but it never got solved.

EXCEPT function not returning the correct details - Microsoft Power BI Community

If there is a better way of implementing this to get the data that I want, please let me know.

[johnt75 · Accepted solution]

The syntax for the SELECTCOLUMNS statements is wrong. After each "ID" you have a table reference and then the column reference, you should just have the column reference.

A simpler way of doing it is

VAR ThisMonth = CALCULATETABLE(
	DISTINCT( 'Table'[ID]),
	'Table'[Source Date] = DATE(2022, 12, 1)
)
VAR LastMonth = CALCULATETABLE(
	DISTINCT( 'Table'[ID]),
	'Table'[Source Date] = DATE(2022, 11, 1)
)
RETURN EXCEPT( LastMonth, ThisMonth)

[ampams1]

Hello again @johnt75,

Now would it be possible to count the number of rows of this resulting table and input that result for a table like this?

Source Date	Number of Closed Processes
1/11/2022	0
1/12/2022	1
1/1/2022	2

Which essentially dynamically repeating this calculation between the two dates in the source date column, rather than manually inputting DATE(YYYY,MM,DD ) .
It would be amazing if that was possible!

[johnt75]

Try

Closed processes =
SUMX (
    VALUES ( 'Table'[Source date] ),
    VAR CurrentDate =
        SELECTEDVALUE ( 'Table'[Source date] )
    VAR PrevDate =
        EOMONTH ( CurrentDate, -2 ) + 1
    VAR ThisMonth =
        CALCULATETABLE ( DISTINCT ( 'Table'[ID] ), 'Table'[Source Date] = CurrentDate )
    VAR LastMonth =
        CALCULATETABLE ( DISTINCT ( 'Table'[ID] ), 'Table'[Source Date] = PrevDate )
    RETURN
        COUNTROWS( EXCEPT ( LastMonth, ThisMonth ) )
)

[ampams1]

Hi @johnt75,
I got an error saying "The function SUMX cannot work with values of type String"

Did I translate your solution correctly? ClosedProcesses is the name of the table where I would store the results and use to update the date filters

SUMX(

    VALUES ('ClosedProcesses'[SourceDate]),

    VAR CurrentDate =

        SELECTEDVALUE ( 'ClosedProcesses'[SourceDate])

    VAR PrevDate =

        EOMONTH ( CurrentDate, -2 ) + 1

    VAR ThisMonth =

        CALCULATETABLE ( DISTINCT ('Master Process Data'[ID]), 'Master Process Data'[Source Date] = CurrentDate )

    VAR LastMonth =

        CALCULATETABLE ( DISTINCT ('Master Process Data'[ID]), 'Master Process Data'[Source Date] = PrevDate )

    RETURN

        EXCEPT ( LastMonth, ThisMonth )

)

[johnt75]

My mistake, I forgot to put in the COUNTROWS. I've edited my post to include it

[ampams1]

Hi @johnt75 
I adjusted it with the countrows and it gives blanks

[johnt75]

It gives me 1 for December using the sample data you posted. You could use my original code in DAX Studio to check different months and see if there are closed processes.

[ampams1]

Hi @johnt75,
Thanks for all your help so far. Unfortunately I don't have access to DAX Studio for my organization so I will manually implement your original solution each month.

[ampams1]

Thank you very much for figuring it out. I got the exact data that I needed. I am 2 weeks new to PowerBI and it has a steep learning curve, particularly with understanding how CALCULATE/CALCULATETABLE work.