Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table with a list of companies and their start and end dates:
I am trying to figure out the best way to create a calculated column that will show their durations in 1 year intervals. So I'm looking for categories of:
With the final category being 10+ Years. Also, my data is dynamic so everyday the current date is updated. So for instance once a company has been with us for over 3 years, their duration is updated from 2-3 to 3-4. I'm sure it's some kind of switch statement but I haven't quite been able to figure out how to get it right. Thanks!
Solved! Go to Solution.
Hi @Anonymous
You can do it with the help of a small PARAMETER TABLE... such as below
Copy paste the Table below to your Power BI Desktop and name it "PARAMETERS"
Category | Start | End |
0-1 years | 0 | 1 |
1-2 years | 1 | 2 |
2-3 years | 2 | 3 |
3-4 years | 3 | 4 |
4-10 years | 4 | 10 |
10+ years | 10 | 1000 |
Now assuming your TableName is Table1,, you can add this calculated column to your Table 1 to get the categories
Category = VAR No_of_Years = DATEDIFF ( Table1[Start], Table1[End], DAY ) / 365 RETURN CALCULATE ( FIRSTNONBLANK ( Parameters[Category], 1 ), FILTER ( Parameters, No_of_Years >= Parameters[Start] && No_of_Years < Parameters[End] ) )
Hi @Anonymous
You can do it with the help of a small PARAMETER TABLE... such as below
Copy paste the Table below to your Power BI Desktop and name it "PARAMETERS"
Category | Start | End |
0-1 years | 0 | 1 |
1-2 years | 1 | 2 |
2-3 years | 2 | 3 |
3-4 years | 3 | 4 |
4-10 years | 4 | 10 |
10+ years | 10 | 1000 |
Now assuming your TableName is Table1,, you can add this calculated column to your Table 1 to get the categories
Category = VAR No_of_Years = DATEDIFF ( Table1[Start], Table1[End], DAY ) / 365 RETURN CALCULATE ( FIRSTNONBLANK ( Parameters[Category], 1 ), FILTER ( Parameters, No_of_Years >= Parameters[Start] && No_of_Years < Parameters[End] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
103 | |
101 | |
76 | |
66 | |
61 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |