Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DATADIFF as measure

Good day,

 

the following formula works fine as a calculated column, but not as a measure. When do I have to change if I wanted to have this calculation done by a measure?

Runtime = DATEDIFF([StartedAt];[StoppedAt];SECOND)

Both columns are of DateTime type (they contain date + time).

 

Or is it that one cannot use DATEDIFF in a measure? I have noticed when I type for example measure = MAX([ then I get a dropdown list of all available columns, but when I type measure = DATEDIFF([ nothing happens, and when I type the column name manually, it isn't recognized.

 
Regards,
mho-de
 
P.S. I hope I'm using the right terms here. I'm using a German version of Power BI and all functions and names are in German
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , you can create as the measure then you may have to take min/max on each side and force a row context.

    like

    sumx(Table,DATEDIFF(Table[StartedAt];Table[StoppedAt];SECOND))
    avergageX(Table,DATEDIFF(Table[StartedAt];Table[StoppedAt];SECOND))

    avergageX(values(Table[ID]),DATEDIFF(Table[StartedAt];Table[StoppedAt];SECOND))

    or

    Like

    sumx(summarize(Table,Table[ID],"_1",DATEDIFF(min(Table[StartedAt]);max(Table[StoppedAt];SECOND))),[_1])

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , you can create as the measure then you may have to take min/max on each side and force a row context.

    like

    sumx(Table,DATEDIFF(Table[StartedAt];Table[StoppedAt];SECOND))
    avergageX(Table,DATEDIFF(Table[StartedAt];Table[StoppedAt];SECOND))

    avergageX(values(Table[ID]),DATEDIFF(Table[StartedAt];Table[StoppedAt];SECOND))

    or

    Like

    sumx(summarize(Table,Table[ID],"_1",DATEDIFF(min(Table[StartedAt]);max(Table[StoppedAt];SECOND))),[_1])

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello amitchandak,

       

      thank you for your suggestions! This one proved the one I was looking for:

      sumx(Tablename,DATEDIFF(Tablename[StartedAt];Tablename[StoppedAt];SECOND))

       

      I would have never thought of doing it this way, but then again, I only started to use PowerBI yesterday. I used to work with BOXI for several years already, so I tried to apply the philosophy of BOXI to PowerBI. But I must think differently here 🙂

       

      mho

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    HI Anonymous ,

     

    I have used DATEDIFF in measures before. Seeing your formular below:

    Runtime = DATEDIFF([StartedAt];[StoppedAt];SECOND)

     

    Looks like the highlighted ones are measures. In order to create a measure using DATEDIFF, you need to replace these highlighted measure with some datetime columns along with some aggregation functions - MAX, MIN

    Refer screenshot for example as below:

    If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

     

    Thanks,

    Pragati

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello Pragati,

       

      I should have been more detailed. No, those two fields are not measures. I have imported the data from a normal Excel file and those two fields are regular columns from the table.

       

      I started on PowerBI only yesterday. I usually work on BOXI and SSRS, but now we are to have a look into PowerBI. So I tought that a measure is a normal, calculated variable. It seems to me that measures are more like aggregating variables, hence the need to use functions such as min/max. I appreciate your help, though!