Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
52 | |
27 | |
24 | |
13 | |
9 |
User | Count |
---|---|
75 | |
58 | |
47 | |
16 | |
12 |