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. 

verolure's avatar
verolure
New Member
1 year ago

Hi Anonymous,

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.