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
jb123
Frequent Visitor

Measure to Calculate the number of days between two date columns

I am trying to do something that is very easy in Excel but haven't found the correct syntax in Power BI yet. 

 

I need to create a measure that calcualtes the number of days between two dates.  I am not looking to do anything special like network days just plain old calendar days between dates in Column A to dates in Column B.  What is the simpliest way to calulate this in Power BI?

 

Thanks.

 

Judie 

 

 

1 ACCEPTED SOLUTION
jb123
Frequent Visitor

That worked!  Thank you Smiley Happy

View solution in original post

13 REPLIES 13
Sean
Community Champion
Community Champion

@jb123  That would be the DATEDIFF function => DATEDIFF ( <star_date>, <end_date>, <interval> )

 

An error is returned if start_date is larger than end_date.

 

To avoid this error use this formula in a Calculated Column

 

Days Column = 
SWITCH (
    TRUE (),
    'Table'[start_date] < 'Table'[end_date], DATEDIFF ( 'Table'[start_date], 'Table'[end_date], DAY ),
    'Table'[start_date] > 'Table'[end_date], DATEDIFF ( 'Table'[end_date], 'Table'[start_date], DAY ) * -1,
    0
)

 

You multiply * -1 to get negative numbers where the event ended before it started (implying time travel Smiley Happy)

Here's the result...

 

DATEDIFF Function.png

 

Hope this helps! Smiley Happy

 

Burk
Frequent Visitor

Hello,

 

My table has no start and end date. In my data table has got one date column. When i get table it seems like that;

 

IDVisited CountryDate
12345USA9.May
12345USA10.May
12345USA11.May
12345USA12.May
54321UK3.May
54321UK4.May
54321UK5.May
54321UK6.May

 

But instead of which days i need to show;

 

IDVisited CountryCalculated Days
12345USA4
54321UK6

 

Thank You!

Hi Sean

 

Could not quite figure it out until I discovered your post - works a treat.  Thanks 🙂

jb123
Frequent Visitor

That worked!  Thank you Smiley Happy

Hi @jb123,

Please mark the right reply as answer, so the people who have similar issue can find solution easily and clearly. Thanks a lot.

Best Regards,
Angelia

I am not sure how it's working for you but when applied exactly the same formula for a new measure it does not allow me to put the column names like 'start date' and 'end date'. It only accept the formula or measure already created...Any suggestions?

Sean
Community Champion
Community Champion

@kb

The above formula is for a Calculated Column

When you try to create a Measure

Intellisense does not allow you to select a column because you need an aggregator first such as MIN or MAX

So here is the Measure formula

Days Measure = 
SWITCH (
    TRUE (),
    MIN('Projects'[start_date]) < MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[start_date]), MIN('Projects'[end_date]), DAY ),
    MIN('Projects'[start_date]) > MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[end_date]), MIN('Projects'[start_date]), DAY )* -1
)

And here's the result...

DATEDIFF.gif

Hope this helps! Smiley Happy

First, thanks for this.. as close as I've come to solving the problem. However, it doesn't quite work as expected with my data and it may be that it has something to do with the two tables ('basic') and ('statuschanges') involved. The are related, but when I use this formula, the "Days Measure" column returns a constant 365 for all records:

 

Days Measure =

SWITCH (

   TRUE (),

   MIN('basic'[submissiondate]) < MIN('statuschanges'[statusdate]), DATEDIFF ( MIN('basic'[submissiondate]), MIN('statuschanges'[statusdate]), DAY ),

   MIN('Projects'[submissiondate]) > MIN('statuschanges'[statusdate]), DATEDIFF ( MIN('basic'[submissiondate]), MIN('statuschanges'[statusdate]), DAY )* -1)

 

at the bottom of the screen, I get the message "TABLE: basic (107,846 rows) COLUMN: Days Measure (1 distinct values)"

Any help would be appreciated!

Thanks ~bee

re: "the "Days Measure" column returns a constant 365 for all records":

 

Double check the relationships and aggregated fields to make sure you're displaying the correct field. Often when a field exists in multiple linked tables it's possible to reference the wrong field, in which case you'll get the same value for all records.

I am trying to use your basic formula, my version is :

DATEDIFF 20180207.jpg

 

Days Last Update = 
SWITCH (
    TRUE (),
    MIN('Production Approval Workflow'[Status_InFlight])=TRUE, DATEDIFF (MIN('Production Approval Workflow'[Modified]), today(), DAY ),
    MIN('Production Approval Workflow'[Status_InFlight])=FALSE, ""
)

 

 

But when I drop it into a table, I get the "Can't display the visual" error.

Note: The field [Modifed] iis a date/time stamp.

Also - why don't the table names and field values prepopulate when I am creating a formula?

kb
Frequent Visitor

Yes, Now it's working fine 🙂 Thank you

jb123
Frequent Visitor

What is the correct syntax?  Assume I am trying to create a Column and have the number of days between the dates in two columns.  DateDiff requires an interval like month, year, day, etc.  When I try to create a column using the following

 

Column = DATEDIFF(Table1[Dates1], Table1[Dates2], DAY)

 

I get an error that the start date cannot be greater than the end date.  I switch the order of dates columns and it still returns the same error. 

Sean
Community Champion
Community Champion

Even if you have only 1 row where the end_date is before the start_date you'll get an error for the entire column!

 

So use the above formula and the negative numbers in the results will show you where that error(s) is(are).

 

Good Luck! Smiley Happy

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.