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 :idbutypedate20810red6/18/202420910red6/18/202421010red6/18/202421110red6/18/202420910red6/19/202421020red6/19/202421110blue6/19/202421210red6/19/2024 Here is the final table wanted: idbutypedateexist_priorexist_newtransfer_intransfer_outconvertedremovedending20810red6/18/202410000-1020910red6/18/2024100000021010red6/18/2024100-100021110red6/18/20241000-10020910red6/19/2024000000121020red6/19/2024001000121110blue6/19/2024000010121210red6/19/20240100001 id - whole number bu - whole numbertype - textdate - 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/19examples: id in 208, 209, 210, and 211exist_new = if the 'id' only exists in the latest date (6/19), then put 0 in 6/18, and put 1 in 6/19example: eid = 212transfer_in = if 'id' exists in both dates and bu changed, put 0 in 6/18, and put 1 in 6/19example: id = 210transfer_out = if 'id' exists in both dates and bu changed, put -1 in 6/18, and put 0 in 6/19example: id = 210converted = if 'id' exists in both dates and type changed, put -1 in 6/18, and put 1 in 6/19example: id = 211removed = if 'id' exists in only previous date, put -1 in 6/18.example: id = 208ending = 1 for latest date (6/19), and 0 for previous date (6/18)examples: id in 209, 210, 211 and 212Thank you so much for your advice and help!

Forum Discussion

zenz

Frequent Visitor

2 years ago

Solved

Record exists in previous date and flag accordingly

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

3 Replies

zenz
Frequent Visitor
2 years ago
mark_endicott Thank you so much!

mark_endicott

Super User

2 years ago

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.

zenz
Frequent Visitor
2 years ago
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:

Forum Discussion

Record exists in previous date and flag accordingly

3 Replies

Related content

Sorting Records

Remove records based on previous records

Previous Record

Counting Non-Blank Records

Measures count not segregating accordingly in visualization