Forum Discussion
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...
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.
SachinNandanwar
Impactful Individual
Impactful Individualverolure : 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 ) ) ) )