cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KWcpoint
Frequent Visitor

Datediff not working on all rows

I am calculating the number of months between two dates on a table of around 100,000 rows.

 

I'm using this forumula; Term.Months = DATEDIFF(Elements[Start Date],dateadd(Elements[End Date],1,day),MONTH)

 

About 1,500 rows are showing no results, blank.  I am getting good calcs for most of the rows; and the terms vary between 0 and 64 months.

DatediffIssue.PNG

 

3 REPLIES 3
shebr
Resolver III
Resolver III

Hi @KWcpoint

 

Can you post an image of some of the dates and columns with no data? the image you posted is cropped.

 

Thanks

 

shebr

KWcpoint
Frequent Visitor

The image shows 3 columns, start.date, end.date and term; term is the column that is blank (the issue I'm experiencing).  The datediff calc uses the first two columns, to create the 3rd.

Hi @KWcpoint

 

Apologies I saw some items were cropped and assumed that was part of the column.

 

Anyhow, fw things to check. Your PBI Desktop version, ensure you are up to date.

 

Check your region settings in the Options section of your PBI desktop application. It could be that your dates are being determined as dd/mm/yyyy rather than mm/dd/yyyy. Also try changing the columns to a short date format (for testing purposes) to see if that helps.

 

Let me know how you get on.

 

Thanks

 

shebr

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors
Users online (448)