Company Durations

[Anonymous]

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:

• 1-2 Years
• 2-3 Years
• 3-4 Years

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!

[Zubair_Muhammad · Accepted 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]
        )
    )