Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Caitlin_Knox
Advocate III
Advocate III

Date Calculations - Resulting Data Type

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())

1 ACCEPTED SOLUTION
ashishrj
Power Participant
Power Participant

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

format.png

View solution in original post

6 REPLIES 6
Armenni
Frequent Visitor

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
Power Participant
Power Participant

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

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

format.png

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
Power Participant
Power Participant

@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!

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.

1.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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