Record exists in previous date and flag accordingly

[zenz]

Hi there, 

I am using Power BI Desktop: Version: 2.130.930.0 64-bit (June 2024)

I need to create a 7 new columns / measures in DAX to analyze the changes by comparing the latest date data with data in exactly 1 day prior. In my case here, I only want to compare data in 6/19 with data in 6/18, and don't want to compare to data in 6/17 (for example). 

here is the raw table :

id	bu	type	date
208	10	red	6/18/2024
209	10	red	6/18/2024
210	10	red	6/18/2024
211	10	red	6/18/2024
209	10	red	6/19/2024
210	20	red	6/19/2024
211	10	blue	6/19/2024
212	10	red	6/19/2024

Here is the final table wanted: 

id	bu	type	date	exist_prior	exist_new	transfer_in	transfer_out	converted	removed	ending
208	10	red	6/18/2024	1	0	0	0	0	-1	0
209	10	red	6/18/2024	1	0	0	0	0	0	0
210	10	red	6/18/2024	1	0	0	-1	0	0	0
211	10	red	6/18/2024	1	0	0	0	-1	0	0
209	10	red	6/19/2024	0	0	0	0	0	0	1
210	20	red	6/19/2024	0	0	1	0	0	0	1
211	10	blue	6/19/2024	0	0	0	0	1	0	1
212	10	red	6/19/2024	0	1	0	0	0	0	1

id - whole number 

bu - whole number

type - text

date - date

the 7 new columns / measures wanted are defined as follows: 

• exist_prior = if the 'id' exists in the previous date (6/18), then put 1 in the line of the 6/18, and put 0 in the line of the 6/19
  • examples: id in 208, 209, 210, and 211
• exist_new = if the 'id' only exists in the latest date (6/19), then put 0 in 6/18, and put 1 in 6/19
  • example: eid = 212
• transfer_in = if 'id' exists in both dates and bu changed, put 0 in 6/18, and put 1 in 6/19
  • example: id = 210
• transfer_out = if 'id' exists in both dates and bu changed, put -1 in 6/18, and put 0 in 6/19
  • example: id = 210
• converted = if 'id' exists in both dates and type changed, put -1 in 6/18, and put 1 in 6/19
  • example: id = 211
• removed = if 'id' exists in only previous date, put -1 in 6/18.
  • example: id = 208
• ending = 1 for latest date (6/19), and 0 for previous date (6/18)
  • examples: id in 209, 210, 211 and 212

Thank you so much for your advice and help! 

[zenz]

@mark_endicott , 

I successfully created the columns using your codes. thank you again.

however, when i tried to create them as measures, i am not getting the results I want (see bottom for screenshot).

And I changed the logic for exist_prior a little bit: as long as the id is not null, and the date is 1 day prior to the max date in the whole table. 

can you please take a look at the syntax below to see where I get it wrong, and correct the measure? 

measure syntax: 

measure_exist_prior =

VAR date_prior = MAX('test'[date]) - 1

RETURN  

    if(        

        NOT(ISBLANK( SELECTEDVALUE(test[id] ) ) ) && SELECTEDVALUE( test[date] ) = date_prior, 1,0

    )

column syntax: 

column_exist_prior =

VAR date_prior = MAX('test'[date]) - 1

RETURN  

    if(        

        NOT(ISBLANK( 'test'[id]) ) &&  'test'[date] = date_prior, 1,0

    )

For the "if" statement, I thought it goes through the table row by row and test the statement, which is true in "column", but that is not the case in measure i guess. I do understand that the measure is doing a aggregation, but how do i get it to examine just 1 row with the variable given. 

Here is what i get for the "exist_prior" with measure (not correct) and column (correct) in a table visual: 

[zenz]

@mark_endicott Thank you so much! 

[mark_endicott · Accepted solution]

@zenz - If you want these as calculated columns, please use the below DAX:

exist_prior = 
VAR _id = 'Table (2)'[id]
VAR date_prior = 'Table (2)'[date] + 1
VAR id_prior = CALCULATE(MAX( 'Table (2)'[id] ), REMOVEFILTERS( 'Table (2)' ), 'Table (2)'[date] = date_prior , 'Table (2)'[id] = _id)

RETURN  
    if( ISBLANK(id_prior),0,1)

-------------------

exist_new = 
VAR _id = 'Table (2)'[id]
VAR date_prior = 'Table (2)'[date] -1
VAR id_prior = CALCULATE(MAX( 'Table (2)'[id] ), REMOVEFILTERS( 'Table (2)' ), 'Table (2)'[date] = date_prior, 'Table (2)'[id] = _id )
VAR min_date = CALCULATE( min( 'Table (2)'[date] ), REMOVEFILTERS( 'Table (2)' ))
RETURN  
    if( ISBLANK( id_prior ),if('Table (2)'[date] = min_date,0,1),0)

-------------------

Transfer In = 
VAR _id = 'Table (2)'[id]
VAR _bu = 'Table (2)'[bu]
VAR date_prior = 'Table (2)'[date] +1
VAR id_prior = CALCULATE(MAX( 'Table (2)'[id] ), REMOVEFILTERS( 'Table (2)' ), 'Table (2)'[date] = date_prior, 'Table (2)'[id] = _id, 'Table (2)'[bu] <> _bu )

RETURN  
    if( ISBLANK( id_prior ),0,1)

--------------------

Transfer Out = 
VAR _id = 'Table (2)'[id]
VAR _bu = 'Table (2)'[bu]
VAR date_prior = 'Table (2)'[date] -1
VAR id_prior = CALCULATE(MAX( 'Table (2)'[id] ), REMOVEFILTERS( 'Table (2)' ), 'Table (2)'[date] = date_prior, 'Table (2)'[id] = _id, 'Table (2)'[bu] <> _bu )

RETURN  
    if( ISBLANK( id_prior ),0,-1)

---------------------

VAR _id = 'Table (2)'[id]
VAR _type = 'Table (2)'[type]
VAR date_prior = 'Table (2)'[date] -1
VAR id_prior = CALCULATE(MAX( 'Table (2)'[id] ), REMOVEFILTERS( 'Table (2)' ), 'Table (2)'[date] = date_prior, 'Table (2)'[id] = _id, 'Table (2)'[type] <> _type )
VAR date_future = 'Table (2)'[date] + 1
VAR id_future = CALCULATE(MAX( 'Table (2)'[id] ), REMOVEFILTERS( 'Table (2)' ), 'Table (2)'[date] = date_future, 'Table (2)'[id] = _id, 'Table (2)'[type] <> _type )

RETURN  
    SWITCH( TRUE() , id_prior = 'Table (2)'[id], 1, id_future = 'Table (2)'[id], -1 ,0 )

-------------------------

Removed = 
VAR _id = 'Table (2)'[id]
VAR date_prior = 'Table (2)'[date] +1
VAR id_prior = CALCULATETABLE(VALUES('Table (2)'[id]), REMOVEFILTERS( 'Table (2)' ), 'Table (2)'[date] = date_prior , 'Table (2)'[id] = _id)
VAR id_in_prior = CALCULATE( MAX( 'Table (2)'[id] ), not('Table (2)'[id]) in { id_prior })
VAR max_date = CALCULATE( MAX( 'Table (2)'[date] ), REMOVEFILTERS( 'Table (2)' ))
RETURN  
    if( ISBLANK( id_in_prior ), 0,if('Table (2)'[date] = max_date, 0,-1 ))

-------------------------

Ending = 

VAR max_date = CALCULATE( MAX( 'Table (2)'[date] ), REMOVEFILTERS( 'Table (2)' ))

RETURN  
    if( 'Table (2)'[date] = max_date,  1,0 )

Screen shot shows they work as requested:

If you would like to make them measures, you just need to bt every 'Table'[Column]' reference in a filter or VAR inside SELECTEDVALUE - e.g. VAR _id =  SELECTEDVALUE( 'Table (2)'[id] )

If these work for you, pelase markas the solution.