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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.