cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maracles
Resolver II
Resolver II

Column data type locked to Date

I have three tables.

1. Order Facts with a date column called First Published. This stores a date and Power BI, as expected, allows me to change the format on the modelling tab to whatever I want e.g. Whole Number.

2. Work Records with a date column called First AssignedThis stores a date and Power BI, as expected, allows me to change the format on the modelling tab to whatever I want e.g. Whole Number.

3. A link table called Unique Work Records. This is a two column table with all the unique work records by OrderID. This column is then used to link to the other two tables. The second column is First Assigned and is produced using a RELATED() function to extract the MIN([First Assigned]). Again, this stores a date and Power BI, as expected, allows me to change the format on the modelling tab to whatever I want e.g. Whole Number.

I have then created three measures:

 

 

  • First Assigned Date Max = MAX(Order Facts[First Assigned Date])

  • First Published Date Max = Max(Unique Work Records[First Published Date]

  • Time to Delegation = [First Published Date Max] - [First Assigned Date Max]


My goal is to have this final measure give the number of days between the other two dates and it does seem to work however it will only return the value as a date. When I go to the modelling ta for that measure I cannot select a different data type so I cannot show it as a whole number i.e. the number of days.

Can anyone help?

Thanks. 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

Try adding a "*1" to your formula for the final measure and see if that helps:

 

Time to Delegation = ([First Published Date Max] - [First Assigned Date Max]) * 1

Or use DATEDIFF with an interval of DAYS:

https://msdn.microsoft.com/en-us/library/dn802538.aspx

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Greg_Deckler
Super User IV
Super User IV

Try adding a "*1" to your formula for the final measure and see if that helps:

 

Time to Delegation = ([First Published Date Max] - [First Assigned Date Max]) * 1

Or use DATEDIFF with an interval of DAYS:

https://msdn.microsoft.com/en-us/library/dn802538.aspx

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@Greg_Deckler the "*1"  worked great. If you dn't mind, why does that work?

Thank you. 

Forces it to be returned as a number instead of a date type, basically in DAX data type logic: 

Date - Date = Date 

whereas 

Date * Number = Number

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Very useful. Thanks. 

Forces it to be returned as a number instead of a date type, basically in DAX data type logic, Date - Date = Date whereas Date * Number = Number


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors