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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RvdHeijden
Post Prodigy
Post Prodigy

Calculate the difference between 2 dates

I need your help guys.

 

It's a simple question and i hope the anwer is simple

 

I got dates formatted like 3-2-2017 08:00:00 and 3-2-2017 12:13:00 and i need the difference in minutes if that is possible

2 ACCEPTED SOLUTIONS
Sean
Community Champion
Community Champion

Sean
Community Champion
Community Champion

@RvdHeijden


@RvdHeijden wrote:

it states that in DATEDIFF function, the start date cannot be greater than the end date


 

If you used my formula you should not be getting this error!

See picture...

if start before end +

if end before start -

If both are blank you get zero

if either one is blank you get blank

But no you can mix text and number data types in the same column!

(meaning you can't get numbers when the data is there and then text when its not - but you will get blanks or zero)

 

DATEDIFF - Minute.png

 

Hope this helps!

Good Luck! Smiley Happy

View solution in original post

5 REPLIES 5
Sean
Community Champion
Community Champion

@RvdHeijden  Look at my response here

http://community.powerbi.com/t5/Desktop/Measure-to-Calculate-the-number-of-days-between-two-date-col...

Just change DAY to MINUTE in the formula

 

Good Luck!Smiley Happy

@Sean

Another question because your formula works if both dates have values.

The formula returns an error it seems if one of the 2 dates are empty.

 

it states that in DATEDIFF function, the start date cannot be greater than the end date

 

How should i edit the formula so that if one of the 2 dates are empty it returns a value like 'missing data' or something like that ?

Sean
Community Champion
Community Champion

@RvdHeijden


@RvdHeijden wrote:

it states that in DATEDIFF function, the start date cannot be greater than the end date


 

If you used my formula you should not be getting this error!

See picture...

if start before end +

if end before start -

If both are blank you get zero

if either one is blank you get blank

But no you can mix text and number data types in the same column!

(meaning you can't get numbers when the data is there and then text when its not - but you will get blanks or zero)

 

DATEDIFF - Minute.png

 

Hope this helps!

Good Luck! Smiley Happy

@Sean i copied this formula in another colum and changed the colums for the formula, nothing else but it still reads as an #ERROR.

In DATEDIFF function, the start date cannot be greater than the end date

 

The new colums only have date values and no tekst

 

 

DATEDIFF Hersteltijd =
SWITCH (
    TRUE ();
    Tickets[Monteur gearriveerd] < Tickets[Technisch gereed]; DATEDIFF ( Tickets[Technisch gereed]; Tickets[Monteur gearriveerd]; MINUTE );
    Tickets[Technisch gereed] > Tickets[Monteur gearriveerd]; DATEDIFF ( Tickets[Monteur gearriveerd]; Tickets[Technisch gereed]; MINUTE ) * -1;
    0
)

@Sean i found the reason why for formula returns an error and i used the wrong colums in the formula.

I corrected the mistake and now it works flawlessly

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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