Max interval of days between two dates on date column

[filipeoliveira]

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.

[Jihwan_Kim · Accepted solution]

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

[filipeoliveira]

Thanks! It worked for me.

[amitchandak]

@filipeoliveira , A new column

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

or

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