Forum Discussion

Giavo's avatar
Giavo
Helper III
8 years ago
Solved

DATEDIFF between Rows and GROUP BY

Hello everybody. 2 DAX Questions about DATEDIFF. 1)I need to find the difference in minutes between the rows of "DateTime" column 2)I need to find the difference in minutes between the rows of "Dat...
Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    here is the output i see on my end from your data

parry2k's avatar
parry2k
Super User
8 years ago

try this,  in query editor, add a index column , then add following measure

 

Minute Diff = 
var prevRowDate = CALCULATE(MAX(x[DateTime]), Filter(ALLSELECTED(x), x[Index] < MAX( x[Index]))) 
return datediff(prevRowDate, max(x[DateTime]), MINUTE)
parry2k's avatar
parry2k
Super User
8 years ago

here is the output i see on my end from your data

  • Giavo's avatar
    Giavo
    Helper III
    8 years ago

    Thank you very much, really great job. Would it be possible a new Column instead of a Measure ?

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    unfortunately not in the same table, we have to create a calculated table.

  • Giavo's avatar
    Giavo
    Helper III
    8 years ago

    oh ok, it is ok then. My next step would be to SUM this minutes per each IWC Number

     

  • parry2k's avatar
    parry2k
    Super User
    8 years ago
    summ of minutes = sumx(x, [Minute Diff])

    add above measure, drop table visual with iwc number and above measure, you will have it. 

  • Menolly's avatar
    Menolly
    Frequent Visitor
    8 years ago

    Hi,

    I'll be really interested by how you create this new table from the date column and the measure.

    I tried many ways but I'm not satisfied by result.

    Thanks

  • benario's avatar
    benario
    New Member
    8 years ago

    Kindly help me understand the formula because i keep getting the erro message that the syntax for '[index]' is incorrect. How is the [index] section of the formula supposed to posted ?