topic Re: Issue with Consecutive Valid Date Calculation in DAX in DAX Commands and Tips

Issue with Consecutive Valid Date Calculation in DAX

verolure — Thu, 14 Nov 2024 18:03:04 GMT

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:

Re: Issue with Consecutive Valid Date Calculation in DAX

Anonymous — Fri, 15 Nov 2024 03:17:15 GMT

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.

Re: Issue with Consecutive Valid Date Calculation in DAX

verolure — Sat, 16 Nov 2024 00:34:25 GMT

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.

Re: Issue with Consecutive Valid Date Calculation in DAX

SachinNandanwar — Sat, 16 Nov 2024 18:51:50 GMT

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 ) ) ) )

Re: Issue with Consecutive Valid Date Calculation in DAX

Anonymous — Mon, 18 Nov 2024 01:56:29 GMT

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.