Forum Discussion

verolure's avatar
verolure
New Member
1 year ago
Solved

Issue with Consecutive Valid Date Calculation in DAX

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...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.