Forum Discussion

maracles's avatar
maracles
Resolver II
10 years ago
Solved

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. 

5 Replies

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion

        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