DATADIFF as measure

[mho-de]

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

[Pragati11]

HI @mho-de ,

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

[mho-de]

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!

[amitchandak · Accepted solution]

@mho-de , 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])

[mho-de]

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