Forum Discussion

s1's avatar
s1
Icon for Helper I rankHelper I
4 years ago
Solved

Aggregate max gap between logs when sliced into date hierarchy

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.

 

 

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

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

3 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support

    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's avatar
      s1
      Icon for Helper I rankHelper I

      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-021
      2022-03-032
      2022-03-013
      2022-03-024
      2022-03-035
      2022-03-046
      2022-03-057
      2022-03-068
      2022-03-079
      2022-03-081
      2022-03-092
      2022-03-103
      2022-03-114
      2022-03-121
      2022-03-132
      2022-03-143
      2022-03-154
      2022-03-165
      2022-03-176
      2022-03-187
      2022-03-198
      2022-03-209
      2022-03-2110
      2022-03-2211
      2022-03-2312
      2022-03-2413
      2022-03-2514
      2022-03-2615
      2022-03-2716
      2022-03-2817
      2022-03-2918
      2022-03-3019
      2022-03-3120
      2022-04-011
      2022-04-021
      2022-04-032
      2022-04-043
      2022-04-051
      2022-04-062
      2022-04-073
      2022-04-084
      2022-04-095
      2022-04-106
      2022-04-117
      2022-04-128
      2022-04-139
      2022-04-1410
      2022-04-151
      2022-04-161
      2022-04-172
      2022-04-183
      2022-04-191
      2022-04-202
      2022-04-213
      2022-04-224
      2022-04-231
      2022-04-242
      2022-04-251
      2022-04-262
      2022-04-273
      2022-04-284

       

      The desired summary table/metric:

      Summarized DateMax Days Since Active
      2022-0320
      2022-0410
      • s1's avatar
        s1
        Icon for Helper I rankHelper I

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

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