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
Victormar
Helper IV
Helper IV

Wrong result using DATESDIFF

Hello community, 

I am facing a problem when calculating datediff from when a contract starts up until today, basically I want to know which year number are we in, but the results looks wrong, is it related to an interval where using "YEAR" in the formula power bi adds or not?

Victormar_0-1708511932930.png

Sometimes it counts like we are in the year 0, sometimes in the 1st, although a year hasn't been completed in either of the cases. 

Just to be clear, I want the count to start from 1. I tried added a +1 to the formula, but since sometimes it count as 0 or 1, it leads to wrong results as well.

Thanks for your time and help 🙂

1 ACCEPTED SOLUTION

@Victormar ,

 

when you make datesdiff by year, it gets the difference between the year only not the whole date, so it takes that what's the different between 2023 to 2024,it's 1, because 23+1 = 24



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Idrissshatila
Super User
Super User

Hello @Victormar ,

 

the result is correct, Datediff function calculate the difference between two dates as per the interval you choose, so in your case you selected by year, so it shows tthe difference between the start date and today by year,

 

so what it is doing is that : start date 2020-01-06 and today which is 2024-02-21, the difference in years is zero because we are in the same year.

 

whereas the difference 2023-11-08 and today 2024-02-21 is 1 because the difference between 2023 and 2024 is 1.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Thanks for your answer @Idrissshatila 

But then, what about the date 2023-11-08? Shouldn't it be 0 as well? Or is it not basing the calculation on a year meaning 365 days, but on going from 2023 to 2024, for example. 

 

@Victormar ,

 

when you make datesdiff by year, it gets the difference between the year only not the whole date, so it takes that what's the different between 2023 to 2024,it's 1, because 23+1 = 24



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Got it, thanks. I guess then the best solution is to do the difference in days and divide by 365? My idea is to calculate a natural year from the start date.

Hello @Victormar  , 

you can specify the interval as you want in the datesdiff function.

 

examples:

( "Year", DATEDIFF ( StartDate, EndDate, YEAR ) ),

( "Quarter", DATEDIFF ( StartDate, EndDate, QUARTER ) ),

( "Month", DATEDIFF ( StartDate, EndDate, MONTH ) ),

( "Week", DATEDIFF ( StartDate, EndDate, WEEK ) ),

( "Day", DATEDIFF ( StartDate, EndDate, DAY )

 

check this https://learn.microsoft.com/en-us/dax/datediff-function-dax

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




thanks!

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.