Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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?
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.
Solved! Go to 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])
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
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!
@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
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |