Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks! It worked for me.
@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)
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |