topic Re: Record exists in previous date and flag accordingly in DAX Commands and Tips

Record exists in previous date and flag accordingly

zenz — Fri, 21 Jun 2024 08:58:45 GMT

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!

Re: Record exists in previous date and flag accordingly

mark_endicott — Fri, 21 Jun 2024 13:04:00 GMT

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.

Re: Record exists in previous date and flag accordingly

zenz — Fri, 21 Jun 2024 17:41:32 GMT

mark_endicott Thank you so much!

Re: Record exists in previous date and flag accordingly

zenz — Wed, 26 Jun 2024 22:59:10 GMT

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: