Column data type locked to Date

[maracles]

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

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. 

[Greg_Deckler · Accepted solution]

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

[maracles]

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

Thank you. 

[Greg_Deckler]

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

[maracles]

Very useful. Thanks. 

[Greg_Deckler]

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