Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I am having lease date in my table
I need to create a calculated column with the following,
Needs to create a category for one year from today, two years from today, three years from today, four years from today and 5+ years from today
Calculated column output as follow
1 year
2 years
3 years
4 years
5+ years
Can anyone please advise how to do that? I tried with DateADD function but not successful
here is the sample pbix file https://we.tl/t-SULP415YmN
Solved! Go to Solution.
@bourne2000 can you confirm with a screenshot of the formula bar?
My code appears to be working fine in your example file.
@bourne2000 something like below should work, if I understand your requirement correctly:
Lease Category =
VAR _Age = DATEDIFF(TODAY(), 'iolp-leases'[Lease date],YEAR)
VAR _Result =
SWITCH(
TRUE(),
_Age >= 5, "5+ years",
_Age >= 4, "4 years",
_Age >= 3, "3 years",
_Age >= 2, "2 years",
_Age >= 1, "1 year",
"Less than 1 year"
)
Return
_Result
@ebeery -Thanks for your reply
I checked the calculation is not correct. It shows everything less than one year
@bourne2000 can you confirm with a screenshot of the formula bar?
My code appears to be working fine in your example file.
That's the same column. I just changed the column name.
@bourne2000 I'm not sure where you're going wrong either, but linked below is my version of your file with the column added and working correctly.
https://drive.google.com/file/d/1k0yDUyHK7Z8QMZATZYR6lvFKtGw2f9zu/view?usp=sharing
@bourne2000 In your first screenshot you showed "Lease Expiration Date", but your screenshot above shows "Lease Effective Date" being used.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |