Date Calculations - Resulting Data Type

[Caitlin_Knox]

I'm having an issue with a calculated column in my model that subtracts a date column from todays date, where I want my end result to be 'days'. The result comes in a date format but I've used this formula before in PowerPivot where I've just had to change the format of the result to a whole number. However, in Desktop all the options for other data types are grayed out as inactive. Can anyone point me in the right direction?

Here's the formula I'm using:

Days Remaining in Contract = IF(AND(OMS[Contract expiration date]>TODAY(),OMS[expire year]<2048),OMS[Contract expiration date]-TODAY(),BLANK())

[Armenni]

Since this is the one who shows up on top of google, I'd like to add another solution.

Doing the calculation on LOAD sometimes doesn't quite do the job, specially if your measure may change depending on how you filter your report.

You CAN format the results in DAX likeso:

FORMAT(MAX('Date Table'[Date]) - MIN('Date Table'[Date]); "General Number")

Cheers

[ashishrj]

That's correct Caitlin, we cannot convert calculated columns in query editor but was expecting the same approach 🙂

[ashishrj · Accepted solution]

@Caitlin_Knox Try doing it in Query Editor as given below.

[Caitlin_Knox]

Thanks for pointing me in the right direction.

The calculated columns aren't available in the query editor, so I simply duplicated the [Contract expiration date] column - marked it as a whole number, then called the copied column in my formula so as to result in a whole number.

Days Remaining in Contract = IF(AND(OMS[Contract expiration date]>TODAY(),OMS[expire year]<2048),OMS[Contract expiration date - Copy]-TODAY(),BLANK())

Thanks again!

[ashishrj]

@Caitlin_Knox Try selecting the newly created column i.e. "Days Remaining in Contract" in your case from Fields (left corner) and then Modeling tab appears on the top where you can select Data Type and Format. Hope this works!

[Caitlin_Knox]

Hi @ashishrj

Thanks for your resposne. I am however aware that is how you would change it. The problem I'm having is that those options are inactive and I can't select them.