Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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())
Solved! Go to Solution.
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
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!
@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.
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |