Issue with Consecutive Valid Date Calculation in DAX

[verolure]

The issue is that the Diff Dates 1 column currently calculates the day difference between consecutive dates for each person, instead of between consecutive valid dates as intended.

Problem:

The goal of the Diff Dates 1 column is to calculate the day difference between two consecutive valid dates for each person in the Data 01 table. However, it is actually calculating the day difference between consecutive dates regardless of validity, which produces incorrect results.

Current Workaround:

I created the Diff Dates 2 column as a workaround. This column calculates the day difference between consecutive dates for each person and only returns the result if the row is marked as valid. While it achieves the goal, this approach is very inefficient.

Can you help me identify the issue in Diff Dates 1? Do you have suggestions for more efficient solutions?

Diff Dates 1 code:

Diff Dates 1 =

VAR CurrentDate = 'Data 01'[Date]

VAR CurrentPersonId = 'Data 01'[PersonID]

VAR MaxPreviousDate =

    CALCULATE (

        MAX(  'Data 01'[Date] ),

        FILTER (

            ALL ( 'Data 01' ),

            'Data 01'[Valid] = "TRUE"

            && 'Data 01'[PersonID] = CurrentPersonId

            && 'Data 01'[Date] < CurrentDate

        )

    )

RETURN

DATEDIFF ( MaxPreviousDate, CurrentDate, DAY )

Diff Dates 2 code:

Diff Dates 2 =

VAR CurrentDate = 'Data 01'[Date]

VAR CurrentPersonId = 'Data 01'[PersonID]

VAR MaxPreviousDate =

    CALCULATE (

        MAX ( 'Data 01'[Date] ),

        FILTER (

            ALL ( 'Data 01' ),

            AND ('Data 01'[Date] < CurrentDate,

            'Data 01'[PersonID] = CurrentPersonId

            )

        )

    )

RETURN

IF('Data 01'[Valid] = "TRUE", DATEDIFF ( MaxPreviousDate, CurrentDate, DAY ))

Data from the table Data 01:

Data from Excel:

[SachinNandanwar]

@verolure : Unsure what issue you are facing but your Diff Dates 2 code seem to get the desired output.

Anyways here is my approach.

[Valid Diff] =
    VAR _PersonId =
        MAX ( 'Data 01'[PersonID] )
    VAR _Date =
        CALCULATE ( MIN ( 'Data 01'[Date] ), 'Data 01'[Valid] = "TRUE" )
    VAR _tbl =
        FILTER (
            ALL ( 'Data 01' ),
            'Data 01'[PersonID] = _PersonId
                && 'Data 01'[Date] < _Date
        )
    RETURN
        ABS ( MAXX ( _tbl, ( DATEDIFF ( _Date, 'Data 01'[Date], DAY ) ) ) )

[verolure]

Hi @v-tangjie-msft,

Thank you for the idea! However, the main issue is that the calculations are being performed even for the rows that are not valid, which I need to avoid. The filter was meant to prevent this, but since it didn't work as I expected, I implemented the validation in the Return instead.

[v-tangjie-msft · Accepted solution]

Hi @verolure ,

With your description you need to filter some data, you can create a slicer visual object to filter, if you need the results to change dynamically then you can change the measure to:

Measure = 
VAR CurrentDate = MAX('Data 01'[Date])
VAR CurrentPersonId = MAX('Data 01'[PersonID])

VAR MaxPreviousDate =
    CALCULATE (
        MAX ( 'Data 01'[Date] ),
        FILTER (
            ALLSELECTED(  'Data 01' ),
            AND ('Data 01'[Date] < CurrentDate,
            'Data 01'[PersonID] = CurrentPersonId
            )
        )
    )
RETURN
IF(MAX('Data 01'[Valid]) = "TRUE", DATEDIFF ( MaxPreviousDate, CurrentDate, DAY ))

If you need the results to be static and unaffected by filtering, then the measures remain unchanged:

If I have misunderstood your needs, please clarify in a follow-up reply.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[v-tangjie-msft]

Hi @verolure ,

Instead of calculated columns, you can create measures, which take up resources only at the time of calculation and will outperform calculated columns.

Measure = 
VAR CurrentDate = MAX('Data 01'[Date])
VAR CurrentPersonId = MAX('Data 01'[PersonID])

VAR MaxPreviousDate =
    CALCULATE (
        MAX ( 'Data 01'[Date] ),
        FILTER (
            ALL ( 'Data 01' ),
            AND ('Data 01'[Date] < CurrentDate,
            'Data 01'[PersonID] = CurrentPersonId
            )
        )
    )
RETURN
IF(MAX('Data 01'[Valid]) = "TRUE", DATEDIFF ( MaxPreviousDate, CurrentDate, DAY ))

If I have misunderstood your needs, please clarify in a follow-up reply.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.