Capture Date of Status Change for Product

[scharern]

I have a table of data that captures information about each product change over time. I need to determine which date Info1 changed. I've been trying to use EARLIER and a calculated column to flag each row as changed or unchanged but am struggling with how to group the EARLIER iteration by product. Here's a simplified example:

Product ID	Transaction Index	Transaction Date	Info1	Info2
Prod1	1	1/1/2017	Domestic	Black
Prod1	2	1/6/2017	Domestic	Black
Prod1	3	2/3/2017	Domestic	Black
Prod1	4	2/4/2017	Import	Black
Prod1	5	4/5/2017	Import	Black
Prod2	1	1/6/2017	Domestic	Silver
Prod2	2	1/9/2017	Import	Silver
Prod2	3	1/18/2017	Import	Silver
Prod2	4	2/9/2017	Import	Silver
Prod2	5	3/10/2017	Import	Silver
Prod2	6	3/11/2017	Import	Silver
Prod3	1	1/12/2017	Domestic	Yellow
Prod3	2	1/13/2017	Domestic	Yellow
Prod3	3	1/14/2017	Domestic	Yellow
Prod3	4	1/15/2017	Domestic	Yellow
Prod3	5	2/16/2017	Domestic	Yellow
Prod3	6	2/17/2017	Domestic	Yellow
Prod3	7	3/8/2017	Domestic	Yellow
Prod3	8	3/19/2017	Domestic	Yellow
Prod3	9	4/20/2017	Import	Yellow

[Girish-Patil]

insted of change i want to show info 2 how should ido that??

[AlB]

Hi @jcbutts 

You probably need to modify only the data types in your columns. Make sure index is an integer and not text.  See it all at work in the attached file.

Flag = 
VAR Previous_ =
    CALCULATE (
        DISTINCT ( Table1[Transaction Date] ),
        Table1[Index] = ( EARLIER ( Table1[Index] ) - 1 ),
        ALLEXCEPT ( Table1, Table1[Product] )
    )
RETURN
    IF ( Table1[Transaction Date] <> Previous_ && NOT ISBLANK(Previous_), "Change" )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[jcbutts]

That worked!  Thank you so much!

[jcbutts]

Hi AIB,

I tried something similar with the data below to try and flag when the date/time changes.  However, I get an error saying that DAX can't compare text and numbers and I should consider VALUE or FORMAT to change one of the two.  How would I modify that code for a date change vs a text change?

Product  Index  Transaction Date

Prod1   1 1/1/2017

Prod1   2 1/1/2017

Prod1   3 2/3/2017

Prod1   4 2/3/2017

Prod1   5 2/5/2017

Prod2   1 1/6/2017

Prod2   2 1/6/2017

Prod2   3 1/6/2017

Prod2   4 1/6/2017

Prod2   5 1/6/2017

Prod2   6 1/6/2017

Prod3  1  1/12/2017

Prod3  2  1/13/2017

Prod3  3 1/13/2017

Prod3  4 1/13/2017

Prod3  5 1/13/2017

Prod3  6 1/13/2017

Prod3  7 1/13/2017

Prod3  8 1/13/2017

Prod3  9 1/13/2017

[AlB · Accepted solution]

Hi @scharern 

From your example I undertand that you want not just change, but change only from domestic to import.If so create a new calc column in your table

Flag =
VAR Previous_ =
    CALCULATE (
        DISTINCT ( Table1[Info1] ),
        Table1[Index] = ( EARLIER ( Table1[Index] ) - 1 ),
        ALLEXCEPT ( Table1, Table1[Product] )
    )
RETURN
    IF ( Table1[Info1] <> Previous_, "Change" )

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[Girish-Patil]

insted of change i want to show info2 how should i do that??

[scharern]

Excellent, thank you for your help @AlB