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

Helper I

## Power BI My Datediff formula keeps on giving me blanks

Hello Everyone,

I am trying to take a formula from excel and translate it into power Bi. It looks like it working but I am getting blanks

Jan 19 Open Age = If(AND('FreshService Data RAW'[Created Date]<= 1/31/2019,'FreshService Data RAW'[Resolved Date] > 1/31/2019),DATEDIFF(1/31/2019,'FreshService Data RAW'[Created Date],Day))

I would love somone help. why is my formula not working?
1 ACCEPTED SOLUTION
Super User

@SamuelTesf Try:

``Jan 19 Open Age = If(AND('FreshService Data RAW'[Created Date]<= DATE(2019,1,31),'FreshService Data RAW'[Resolved Date] > DATE(2019,1,31)),DATEDIFF(DATE(2019,1,31),'FreshService Data RAW'[Created Date],Day))``

@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Super User

@SamuelTesf Try:

``Jan 19 Open Age = If(AND('FreshService Data RAW'[Created Date]<= DATE(2019,1,31),'FreshService Data RAW'[Resolved Date] > DATE(2019,1,31)),DATEDIFF(DATE(2019,1,31),'FreshService Data RAW'[Created Date],Day))``

@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

Thank you so much for the help, I changed it a little to not get negative numbers but it worked perfectly.

Super User

@SamuelTesf Yep.

"1/31/2019" is not interpreted as a date but rather the number (1 / 31) / 2019 = 0.00001597724 which is equivalent to 12/30/1899 12:00:01 AM as a datetime value.

Helper I

That makes sense, Thank you!

## Helpful resources

Announcements

#### 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.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors