Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
mho-de
Regular Visitor

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
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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])

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
Pragati11
Super User
Super User

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:

dateDiff.png

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

 

Thanks,

Pragati

 

 

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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.

TableExample01.png

 

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
Super User
Super User

@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])

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.