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

Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.

Reply
Brett007
Helper III
Helper III

Duration between two dates

I would like to calculate the difference between Today's Date and the Date in the [Assigned Date] Column.

I wanted to present the answer in the form of days. 

 

I used the following formula, but the whole column shows errors.

 

Duration.Days((DateTime.FixedLocalNow())-([Assigned Date]))

 

Any Suggestions

 

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

Hi @Brett007 , 

You could try below M code to see whether it work or not

Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-[Assigned Date]))

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
monsheet
New Member

But then, how so I know which data type both are. They appear to be the same.

Anonymous
Not applicable

Hi @monsheet ,

 

datetime would have hh:mm:ss part, date  doesn't have and doesn't show it. Dates in the column on the screenshot in this case do not have hours and minutes, mean these are in date format.

 

Kind regards,

jb

dax
Community Support
Community Support

Hi @Brett007 , 

You could try below M code to see whether it work or not

Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-[Assigned Date]))

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

gpoggi
Responsive Resident
Responsive Resident

Hi there, assuming your [Assigned Date] is in DateTime format, you only have to add Duration.From to your code, so try something like:

 

Duration.Days(Duration.From(DateTime.FixedLocalNow()-[Assigned Date]))

 

And you should get what you need, please let me know if it works.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Regards,

 

Gian Carlo Poggi

Greg_Deckler
Community Champion
Community Champion

Probably needs to be something like:

 

Number.From([Date2] - [Date1]) + 1

 

?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

I tried, but I did not get a change in the results. 

 
 

 

 

Untitled.png

Anonymous
Not applicable

Hi @Brett007 ,

 

I think the problem is not with the formula - it is in the data type. PBI generally does not support "silent" conversions.

From what I can see the [Assigned Date]  is type date and the DateTime.FixedLocalNow() returns type datetime. These types are not directly compatible and, most likely, you have an error like this:

Expression.Error: We cannot apply operator - to types DateTime and Date.
Details:
    Operator=-
    Left=10/03/2020 8:51:05 AM
    Right=2/01/2020

Either convert the [Assigned Date]  to type datetime or adjust your formula to force datetime to date type converstion:

Date.From(DateTime.FixedLocalNow())

Kind regards,

JB

 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors