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([St...
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])

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