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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kristofferaabo
Helper IV
Helper IV

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
Vvelarde
Community Champion
Community Champion

@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

View solution in original post

v-sihou-msft
Employee
Employee

@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
v-sihou-msft
Employee
Employee

@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,

Vvelarde
Community Champion
Community Champion

@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

Perfect! Thanks a lot, it works! 🙂

K

 

 

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.