Circular Dependency With Date Diff Fail

[win_anthony]

The goal is to create a calculated column which will count the number of days between the last transaction and today. I currently have calculated column which will flag the most recent transaction for each store. From this calculated column, I tried to create another calculated column which will count the number of days between the last transaction flag and today using DATEDIFF. This did not work because "Circular Dependency was detected". Below are references: 1.) The flag which will tell me the latest transaction per store 2.) The failed calculated column which I tried to create 3.) Sample data with current output 4.) Sample data with expected output

Any advice on how I can achieve the goal of creating a calculated column which will count the number of days between the last transaction and today? Your advice is greatly appreciated.

1.) The flag which will tell me the latest transaction per store

latest_welcome_transaction_flag = 
IF( 
    CALCULATE(
        MAX( table[date] ),
        FILTER(
            VALUES( table[date] ),
            table[date] = CALCULATE( 
                                            MAX( table[date] ), 
                                            ALLEXCEPT( table, table[store_id] ) , 
                                            LEFT( table[status], 7 ) = "Welcome" 
                                            )
        )
    ) <> BLANK(),
    1
)

2.) The failed calculated column which I tried to create resulting in "Circular Dependency was detected"

# of Days Since Last Transaction.WelComeOpp = 
IF( table[latest_welcome_transaction_flag] = 1,
    DATEDIFF(
        table[date], TODAY(), DAY 
    )
)

3.) Sample data with current output

store_id	transaction_id	status	date	latest_welcome_transaction_flag
1	100	Welcome	1-Jan-21
1	101	In Process	3-Jan-21
1	102	Canceled	4-Jan-21
1	103	Welcome	10-Jan-21	1
2	104	Welcome	2-Jan-21	1
3	105	Welcome	7-Jan-21
3	106	In Process	11-Jan-21
3	107	Delivery	12-Jan-21
3	108	Returned	13-Jan-21
3	109	Welcome	14-Jan-21	1
4	110	Welcome	1-Feb-21
4	111	Welcome	5-Feb-21
4	112	Welcome	25-Feb-21	1

4.) Sample Data With Expected Output

store_id	transaction_id	date	latest_welcome_transaction_flag	#_days_since_last_transaction
1	103	10-Jan-21	1	61
2	104	2-Jan-21	1	69
3	109	14-Jan-21	1	57
4	112	25-Feb-21	1	15

[OwenAuger · Accepted solution]

Hi @win_anthony 

Try rewriting your first calculated column like this:

latest_welcome_transaction_flag = 
VAR LatestWelcomeDatePerStore = 
    CALCULATE ( 
        MAX ( 'table'[date] ),
        ALLEXCEPT ( 'table', 'table'[store_id] ),
        LEFT ( 'table'[status], 7 ) = "Welcome"
    )
RETURN
    IF ( 'table'[date] = LatestWelcomeDatePerStore, 1 )

I have simplified the code a little, and ensured that the CALCULATE function, which is called within the row context of the table, includes an ALLEXCEPT which removes filters on all columns except store_id.

Originally, the outermost call to CALCULATE added filters corresponding to all columns including # of Days Since Last Transaction.WelComeOpp, which created a circular reference.

Whenever CALCULATE is called in a calculated column, "context transition" causes the values of all columns in the current row are converted to an equivalent filter, unless those columns are excluded from the resulting filter context using ALLEXCEPT/REMOVEFILTERS. 

There is a good article on this general topic here at SQLBI.

Regards,

Owen