cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

How to calculate days/months between to date columns

Hi, I have to columns of dates and with the new aggreagted option in the Jan 17 version of powerBI iI was hoping to see some 'levels' in the dates. Especially the time between the First date in column X and the last date in column Y.

 

Im however struggeling to calcualte the days (months preferebly) between the two columns below ('earlist FPI and Latest LPI'). I have tried the datediff but it returns errors..

 

Any ideas?

 

powerBI question.png

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: How to calculate days/months between to date columns

@Kristofferaabo

 

Hello, try with these measures

 

Diff in Days=
DATEDIFF ( FIRSTDATE ( Table2[Date] ), LASTDATE ( Table2[Date] ), DAY )
Diff in Months=
DATEDIFF ( FIRSTDATE ( Table2[Date] ), LASTDATE ( Table2[Date] ),MONTH )



Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Moderator v-sihou-msft
Moderator

Re: How to calculate days/months between to date columns

@Kristofferaabo

 

What does the error message say?

 

Make sure those two columns are data type, it supposes to work when using DATEDIFF() function.

 

4.PNG

 

55.PNG

 

Regards,

View solution in original post

4 REPLIES 4
Vvelarde Super Contributor
Super Contributor

Re: How to calculate days/months between to date columns

@Kristofferaabo

 

Hello, try with these measures

 

Diff in Days=
DATEDIFF ( FIRSTDATE ( Table2[Date] ), LASTDATE ( Table2[Date] ), DAY )
Diff in Months=
DATEDIFF ( FIRSTDATE ( Table2[Date] ), LASTDATE ( Table2[Date] ),MONTH )



Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Moderator v-sihou-msft
Moderator

Re: How to calculate days/months between to date columns

@Kristofferaabo

 

What does the error message say?

 

Make sure those two columns are data type, it supposes to work when using DATEDIFF() function.

 

4.PNG

 

55.PNG

 

Regards,

View solution in original post

Re: How to calculate days/months between to date columns

Perfect! Thanks a lot, it works! 🙂

K

 

 

Highlighted

Re: How to calculate days/months between to date columns

Another poblem I have is that it will not work if I have some empty cells in the columns.

Can I somehow account not to calculate with where there is blank cells? 

 

Kristoffer

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 174 members 1,705 guests
Please welcome our newest community members: