Showing the longest sequence of date related event

[idolevine]

Hello, so I'm very new to dax and finding a complicated problem that I think requires two loop solution

I have a data table with temperature for every ten minutes, I have date and time together and each seperated. as seen below:

I've added a calculated column to classify events by conditions. 'Event Type'

I'm trying to calculate the longest sequence of a Frost event and how much time it was

So my challenges are:

• If I have two times data can I find the time between them ? Even if they are not on the same day?
• The problem is  that an event can start on one date and continue to the next date.
• If the sequence is stopped, meaning the next row is not 'Event Type' = "Frost" than the count of length goes down to zero. How would I keep the length of the longest count?
• And how can I show it in hours ?
• I thought of creating another calculated column to count the sequence as long as previous row = "Frost" add one to the total, else put 0 and restart the count the next "Frost apperance". It would still not show the time frame of the event.

Many Thanks, If any clarifications needed please write.

[mahoneypat · Accepted solution]

@idolevine Now that the calculated column is in place, you can use a measure like this one to get the time associated with the longest frost.

Time Longest Frost =
VAR frostvalues =
    SUMMARIZE (
        FILTER ( '2017', '2017'[Event Type] = "Frost" ),
        '2017'[Date.Time],
        '2017'[Time To Next Frost]
    )
VAR top1 =
    TOPN ( 1, frostvalues, '2017'[Time To Next Frost], DESC )
VAR result =
    MINX ( top1, '2017'[Date.Time] )
RETURN
    result

Pat

[idolevine]

Thank you,
It actually worked fine for me to make a measure and return the maxium value.
Happy to understand why you suggested this option ?

Again thank you

[idolevine]

@mahoneypat @smpa01 
So after checking Pat's solution it seems to not quite doing the trick. I added the code Pat suggested, the value I'm getting between two dates is the total of frost hours and not the time of the longest events. 

Please help. Thanks

[smpa01]

@idolevine  can you please provide a sample pbix by uploading it in 1/gr drive and share the link here?

[idolevine]

Here, sorry for the delay

https://drive.google.com/file/d/1-m5-4LkRC-WzaBAgBnOOJHzthtOAUk0d/view?usp=sharing

[smpa01]

@idolevine  here is my take on this.

I thought you wanted the Date to be filtered by TOPN(1) and the corresponding strick HOUR

I first added an index column to the datasource

let
    Source = Excel.Workbook(File.Contents("C:\Users\user\Desktop\Daily\longestSequence.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type datetime}, {"Date.Time", type datetime}, {"Temp (°C)", type number}, {"Velocidade do Vento (km/h)", type text}, {"Event Type", type text}, {"Hour", Int64.Type}}),
    #"Extracted Time" = Table.TransformColumns(#"Changed Type",{{"Time", DateTime.Time, type time}}),
    #"Sorted Rows" = Table.Sort(#"Extracted Time",{{"Date.Time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Once you have that, you can write a measure like this

Measure2 =
VAR _minIndex =
    CALCULATE ( MIN ( Sheet2[Index] ), Sheet2[Event Type] = "Frost" )
VAR _maxIndex =
    CALCULATE (
        MIN ( Sheet2[Index] ),
        FILTER ( Sheet2, Sheet2[Index] > _minIndex && Sheet2[Event Type] <> "Frost" )
    ) - 1
VAR _minTime =
    CALCULATE (
        MAX ( Sheet2[Time] ),
        FILTER ( VALUES ( Sheet2[Index] ), Sheet2[Index] = _minIndex )
    )
VAR _maxTime =
    CALCULATE (
        MAX ( Sheet2[Time] ),
        FILTER ( VALUES ( Sheet2[Index] ), Sheet2[Index] = _maxIndex )
    )
VAR _diff =
    DIVIDE ( DATEDIFF ( _minTime, _maxTime, MINUTE ), 60 )
RETURN
    _diff

which gives you this

then you can write a TOPN measure like this

Measure 3 = CALCULATE([Measure2],KEEPFILTERS(TOPN(1,ALLSELECTED(Sheet2[Date]),[Measure2],DESC)))

which would give you this

[idolevine]

Hi,
I tried your solution, aswell as with a slight varation , its seems to be to heavy and there is not enough memory for me to prefrom this operation. Perhpas a more effiecent solution is neccessary as the real data has 250K rows.

Longest Frost Event = 
var _minIndex = 
CALCULATE(
    MIN('All Years'[Index]),'All Years'[Event Type] ="Frost")
var _maxIndex =
 CALCULATE(
     MIN('All Years'[Index]),
     FILTER('All Years','All Years'[Event Type]<> "Frost"),'All Years'[Index]>_minIndex)-1
 var _minTime = 
     CALCULATE( FIRSTNONBLANKVALUE('All Years'[Date.Time],
            FILTER(VALUES('All Years'[Index]),'All Years'[Index]=_minIndex))
     )
var _maxTime =
     CALCULATE( FIRSTNONBLANKVALUE('All Years'[Date.Time],
            FILTER(VALUES('All Years'[Index]),'All Years'[Index]=_maxIndex))
    )
 Var _timediff = 
 DATEDIFF(_minTime,_maxTime,MINUTE)
 Return _timediff

Here is what I tried

[smpa01]

@idolevine will take a look and come back to you by tomorrow.

[mahoneypat]

Here is a column expression that seems to work. This calculation can be very resource intensive, so using the IF in the "nextnotfrost" variable is key to limit it to just the rows where the Event Type = "Frost". Otherwise, you end up doing a calculation on a table with 52k rows 52k times! Since you have only 245 Frost events, this works much faster.

Time To Next Frost =
VAR isfrost = '2017'[Event Type] = "Frost"
VAR thistime =
    IF ( isfrost, '2017'[Date.Time] )
VAR nextnotfrost =
    IF (
        isfrost,
        MINX (
            FILTER (
                FILTER ( '2017', '2017'[Event Type] <> "Frost" ),
                '2017'[Date.Time] > thistime
            ),
            '2017'[Date.Time]
        )
    )
RETURN
    DATEDIFF ( thistime, nextnotfrost, MINUTE ) / 60

Once you add the column, you can sort it descending like below, and then just use a measure that returns the max of that column.

Pat

[idolevine]

@mahoneypat Thanks a lot Pat ! Really helpful.

If I understand correctly your code, you're doing a "count-down" until the end of the frost event. This way, the first row of "Frost" will return the largest number.

I'm wandering if it's possilbe and if there is a difference to do the same but have the frost duration grow. So in a sequence of "Frost" the final row before the "nextnotfrost" will return the largest value. 

Maybe it's indifferent but makes more sense to me this way.

Many thanks dude.

[mahoneypat]

I sorted it descending in the visual just to show the max value. Each row is it's own calculation to the next not frost, so you can sort it any way you prefer.

Pat

[idolevine]

@mahoneypat 

So after checking Pat's solution it seems to not quite doing the trick. I added the code Pat suggested, the value I'm getting between two dates is the total of frost hours and not the time of the longest events. 

Please help. Thanks