Aggregate max gap between logs when sliced into date hierarchy

[s1]

Hello,

I am looking to create a measure that calculates the longest gap since the last log (reduced by filters/slicers). If the earliest log in a timeperiod is not on the first day, it will include looking back before the timeperiod. If the latest log is not on the last day, it will also check the last day in the timeperiod for calculating the longest gap.

I have it working for continuous data, but I am struggling to make it aggrigate the max value within a timeperiod. Is something like this achievable to do?

(ideally I want this as a measure not a column, so that I can filter the data by user/project/property, and get the gaps between the logs based on that filter)

Measure for the continuous data (though may not be done the best)

Days Since Last = 
var minDate0 = MAX(Dates[Date])
var minDate = IF(ISBLANK(MIN(Log[Date])), minDate0, MIN(minDate0, MIN(Log[Date])))
return
CALCULATE(
    DATEDIFF(MAX(Log[Date]), minDate, DAY),
    ALL(Dates[Date]),
    Dates[Date] < minDate
)

Sample data:

Date

2021-03-07

2021-03-11

2021-03-31

2021-04-01

2021-04-04

2021-04-14

Results - top graph is continuous, bottom graph is heirarchy by year/month. The bottom chart should show 21 instead of 9.

[v-easonf-msft]

Hi， @s1 

Your sample data does not appear to correlate with the results of the chart.

Please tell us a bit more about what you are trying to do.

Best Regards,
Community Support Team _ Eason

[s1]

Ah, sorry about that - I manually typed that table. Here is the data, hopefully it makes sense.

The column that is actual data:

Log Date
2022-03-01
2022-03-07
2022-03-11
2022-03-31
2022-04-01
2022-04-04
2022-04-14
2022-04-15
2022-04-18
2022-04-22
2022-04-24

The results from the metric that is working for continuous data:

Date	Days Since Active
2022-03-02	1
2022-03-03	2
2022-03-01	3
2022-03-02	4
2022-03-03	5
2022-03-04	6
2022-03-05	7
2022-03-06	8
2022-03-07	9
2022-03-08	1
2022-03-09	2
2022-03-10	3
2022-03-11	4
2022-03-12	1
2022-03-13	2
2022-03-14	3
2022-03-15	4
2022-03-16	5
2022-03-17	6
2022-03-18	7
2022-03-19	8
2022-03-20	9
2022-03-21	10
2022-03-22	11
2022-03-23	12
2022-03-24	13
2022-03-25	14
2022-03-26	15
2022-03-27	16
2022-03-28	17
2022-03-29	18
2022-03-30	19
2022-03-31	20
2022-04-01	1
2022-04-02	1
2022-04-03	2
2022-04-04	3
2022-04-05	1
2022-04-06	2
2022-04-07	3
2022-04-08	4
2022-04-09	5
2022-04-10	6
2022-04-11	7
2022-04-12	8
2022-04-13	9
2022-04-14	10
2022-04-15	1
2022-04-16	1
2022-04-17	2
2022-04-18	3
2022-04-19	1
2022-04-20	2
2022-04-21	3
2022-04-22	4
2022-04-23	1
2022-04-24	2
2022-04-25	1
2022-04-26	2
2022-04-27	3
2022-04-28	4

The desired summary table/metric:

Summarized Date	Max Days Since Active
2022-03	20
2022-04	10

[s1 · Accepted solution]

Ah, I figured it out. Ended up being simple in the end.

Max Days Since Last = MAXX(Dates, [Days Since Active])