Forum Discussion

harshali's avatar
harshali
Frequent Visitor
9 years ago
Solved

Time Diff between Times in the Same Column

Hi,   I am trying to find the average time between each type of canister change within this table. I am running into some trouble because the dates are in the same column so I cannot just use DATED...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi harshali,

     

    Based on my understanding, you should be able to simply use the formula below to create a measure to calculate the Time Diff, then show the measure with defaultmachine, Canister Type Change, and Type column on the Table visual in your scenario.

     

    Measure = DATEDIFF ( MIN ( Table1[eventdatetime] ), MAX ( Table1[eventdatetime] ), DAY )

     

     

    I know you will feel confused about the solution. So please think about the formula below. :smileyhappy:

     

    (A - B) + (B - C) + (C - D) = A - D

    Hopefully it could help in your scenario.

     

    Regards

fhill's avatar
fhill
Icon for Resident Rockstar rankResident Rockstar
8 years ago

When it comes to Dates & Times, 'Average' is a term with multiple meanings.  I read your reuqest asking for 'How many Days on Average does each canister last?)  Here's a Calcualted Column (not a measure) to help you find the DATEDIFF between the MIN and MAX values.  I then 'Average' by summing the DATEDIFF days and dividing by the UniqueCount of all Types...

 

Thoughts?  P.S.  Your raw data is in the format your provided...  My data screen shot on top just breaks things up into EARLIEST and LATEST so I could do a gut check on my DATEDIFF values.

 

DIFF by Type = CALCULATE(  DATEDIFF(MIN(Table1[eventdatetime]),MAX(Table1[eventdatetime]),DAY), FILTER(Table1, Table1[Type] = EARLIER(Table1[Type])))

 

harshali's avatar
harshali
Frequent Visitor
8 years ago

I am going to test this now. But how did you remove the T in the eventdatetime column?

  • fhill's avatar
    fhill
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Power BI automaticlaly converted that to a Date Time Column for me...?  Not sure if this is a USA formatting thing, as I've seen the "T" allot but never experienced it in my data personally.