Reply
Frequent Visitor
Posts: 7
Registered: ‎01-26-2017
Accepted Solution

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 

 

 


Accepted Solutions
Frequent Visitor
Posts: 7
Registered: ‎01-26-2017

Re: Measure to Calculate the number of days between two date columns

That worked!  Thank you Smiley Happy

View solution in original post


All Replies
Super Contributor
Posts: 1,947
Registered: ‎08-11-2015

Re: Measure to Calculate the number of days between two date columns

[ Edited ]

@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

 

Frequent Visitor
Posts: 7
Registered: ‎01-26-2017

Re: Measure to Calculate the number of days between two date columns

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. 

Super Contributor
Posts: 1,947
Registered: ‎08-11-2015

Re: Measure to Calculate the number of days between two date columns

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

Frequent Visitor
Posts: 7
Registered: ‎01-26-2017

Re: Measure to Calculate the number of days between two date columns

That worked!  Thank you Smiley Happy

Super Contributor
Posts: 1,030
Registered: ‎09-27-2016

Re: Measure to Calculate the number of days between two date columns

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

kb Frequent Visitor
Frequent Visitor
Posts: 4
Registered: ‎03-17-2017

Re: Measure to Calculate the number of days between two date columns

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?

Highlighted
Super Contributor
Posts: 1,947
Registered: ‎08-11-2015

Re: Measure to Calculate the number of days between two date columns

[ Edited ]

@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

kb Frequent Visitor
Frequent Visitor
Posts: 4
Registered: ‎03-17-2017

Re: Measure to Calculate the number of days between two date columns

Yes, Now it's working fine Smiley Happy Thank you