topic Max interval of days between two dates on date column in DAX Commands and Tips

Max interval of days between two dates on date column

filipeoliveira — Mon, 14 Mar 2022 13:39:24 GMT

Hi guys,

I'm trying to calculate the interval of days between a date value and the last date before that date (doing this to all dates in column), and then show the max value of these intervals.

For example:

1. calculate the date intervals between the dates on column:

2. and then return the max value from the intervals calculated above (6 days), in a measure.

Some idea?

Appreciate any help.

Re: Max interval of days between two dates on date column

amitchandak — Mon, 14 Mar 2022 14:44:14 GMT

filipeoliveira , A new column

datediff([Dateref], minx(filter(Table, [Dateref] >earlier([Dateref]) ), [DateRef]) , Day)

datediff([Dateref], maxx(filter(Table, [Dateref] <earlier([Dateref]) ), [DateRef]) , Day)

Re: Max interval of days between two dates on date column

Jihwan_Kim — Mon, 14 Mar 2022 14:46:44 GMT

Hi,

Please check the below picture and the attached pbix file.

I added two more rows in the sample in order to check if it works correctly.

Expected result measure: = VAR newtable = ADDCOLUMNS ( Data, "@previous", CALCULATE ( CALCULATE ( MAX ( Data[DataRef] ), FILTER ( ALL ( Data ), Data[DataRef] < VAR currentdataref = MAX ( Data[DataRef] ) RETURN currentdataref ) ) ) ) VAR intervaltable = FILTER ( ADDCOLUMNS ( newtable, "@interval", INT ( Data[DataRef] - [@previous] ) ), [@previous] <> BLANK () ) VAR maximuminterval = MAXX ( intervaltable, [@interval] ) RETURN maximuminterval

Re: Max interval of days between two dates on date column

filipeoliveira — Mon, 14 Mar 2022 16:35:11 GMT

Thanks! It worked for me.