Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Finding first date in table where column changes value

I have a table with a date column and a number column.

Example:

 

DateNumber
01-01-'214
02-01-'214
03-01-'212

 

I want to add a calculated column that returns the first date where the number is different than the current row.

I've tried using the following DAX formula:

 

Column =
 CALCULATE(
     MIN('Table'[Date].[Date]),
     FILTER('Table', 'Table'[Date].[Date] > EARLIER('Table'[Date].[Date])),
     FILTER('Table', 'Table'[Number] <> EARLIER('Table'[Number]))
)

 

 

As well as:

 CALCULATE(
     MIN('Table'[Date].[Date]),
     FILTER('Table', 'Table'[Date].[Date] > EARLIER('Table'[Date].[Date])
     && 'Table'[Number] <> EARLIER('Table'[Number]))
)

 

 

But this only returns the first date after the current date:

DateNumberColumn
01-01-'21402-01-'21
02-01-'21403-01-'21
03-01-'21204-01-'21

 

And the result I'm looking for is:

DateNumberColumn
01-01-'21403-01-'21
02-01-'21403-01-'21
03-01-'212Some date in the future where [Number] changes

 

Somehow it feels like the second filter in the formula is being ignored.

Any help on how to accomplish this would be greatly appreciated.

  • Anonymous 

    Add the following Calc Column:

    Number Change = 
    var __num = [Number]
    var __date = [Date]
    return
    CALCULATE(
        MIN(Table3[Date]),
        Table3[Number] <> __num,
        Table3[Date] > __date,
        REMOVEFILTERS(Table3)
    )
    

     

2 Replies

  • Anonymous 

    Add the following Calc Column:

    Number Change = 
    var __num = [Number]
    var __date = [Date]
    return
    CALCULATE(
        MIN(Table3[Date]),
        Table3[Number] <> __num,
        Table3[Date] > __date,
        REMOVEFILTERS(Table3)
    )
    

     

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    Changing Date = 
    MINX (
        FILTER (
            INFO,
            INFO[Date] > EARLIER ( INFO[Date] )
                && INFO[Value] <> EARLIER ( INFO[Value] )
        ),
        INFO[Date]
    )