How to Count only distinct years?

[mrthomasshelby]

Hello,

I have a table as shown below:

No	Category	Description	Customer	Industry	Duration	Value	Location	Start Date	End Date	Contact
1	Railings	A	ABCD	Sports	24	500000	Australia	3/25/2010	3/25/2012	Z
2	Railings	B	EFGH	Oil	37	20000	Indonesia	6/12/2012	6/12/2015	Y
3	Railings	C	IJKL	Metals	37	250000	Cambodia	6/12/2012	6/12/2015	X
4	Railings	D	MNOP	Steel	37	450000	Brunei	6/12/2012	6/12/2015	D

Now I want to define a measure called 'Experience' that counts the number of distinct years that the vendor above has provided the services in. For the example above, we can see that the vendor has provided these 4 services in the years: 2010,2011,2012,2013,2014,2015, so the value of 'Experience' will be 6. I am also doing this in the script part for your information. I have tried taking the sum of Year difference between the dates but the problem with this is it sums up even the non-distinct years and gives an inflated value. Please help me out with the logic for this. Thanks in advance!

stalwar1‌ kaushik.solanki‌ tresesco‌

[sunny_talwar · Accepted solution]

Check this

T1:

LOAD No.,

    [Equipment Category],

    Description,

    [Name of Client],

    Industry,

    [Duration (No of months)],

    Value,

    [Location of Services],

    [Start Date],

    [End Date],

    [Client’s Contact Person / Person-in-Charge],

    Year([Start Date]) as StartYear,

    Year([End Date]) as EndYear

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Dates:

LOAD DISTINCT StartYear,

EndYear,

StartYear + IterNo() - 1 as Year

Resident T1

While StartYear + IterNo() - 1 <= EndYear;

[wade12]

T1:

LOAD Vendor,

     No.,

     [Equipment Category],

     Description,

     [Name of Client],

     Industry,

     [Duration (No of months)],

     Value,

     [Location of Services],

     [Start Date],

     Year([Start Date])      as     Year,

     [End Date],

     [Client’s Contact Person / Person-in-Charge]

FROM

(ooxml, embedded labels, table is Sheet1);

[sunny_talwar]

But if you are doing this by Vendor, I would do it like this

T1:

LOAD Vendor,

    No.,

    [Equipment Category],

    Description,

    [Name of Client],

    Industry,

    [Duration (No of months)],

    Value,

    [Location of Services],

    [Start Date],

    [End Date],

    [Client’s Contact Person / Person-in-Charge],

    Year([Start Date]) as StartYear,

    Year([End Date]) as EndYear

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Dates:

LOAD DISTINCT Vendor,

     StartYear + IterNo() - 1 as Year

Resident T1

While StartYear + IterNo() - 1 <= EndYear;

[mrthomasshelby]

Works like a charm! Understood it now! Thanks again Sunny!