Service obligation based on built year of last built

[WLFRD]

Hello,

Maybe someone can help me out. I would like to count the Y and N in the ‘For Service?’ column.

Based on the ‘type’ column and year (or last built), the last column is filled with a Y or N.

These are the rules per type

CB = When there is a year in the cell: Year + 10, if not, Last Built + 10 

Service Obligation: Year + 10 or Last Built +10 < Current year -> Service Obligation is Y

BT =When there is a year in the cell: Year + 15, if not, Last Built + 15

Service Obligation: Year + 15 or Last Built +15 < Current year -> Service Obligation is Y

WHE = When there is a year in the cell: Year + 15, if not, Last Built + 15

Service Obligation: Year + 15 or Last Built +15 < Current year -> Service Obligation is Y

Data:

Table 1	Table 1	Table 2	Table 3	Table 3
Serial Number	Unit	Year	First Built	Last Built	Type	For Service?
c849t04433	c849		2002	2013	WHE	Y
D849T05073	D849	2018	2014		WHE	Y
c849t04433	c849		2002	2013	WHE	Y
K007E02051	K007	2014	2012	2018	BT	Y
A3C1A05384	A3C1	2020	2019		CB	Y
C456T06364	C456	2016	2009	2016	WHE	Y
E444T04119	E444	2019	2011		WHE	Y
B214E01744	B214	2009	2006	2011	BT	Y
B214E01744	B214	2009	2006	2011	BT	Y
D222E01521	D222	2015	2014	2019	BT	Y
P177B11112	P177	2020	2015		CB	Y
B214E01790	B214	2010	2006	2011	BT	Y
F222E01723	F222	2022	2021		BT	Y
E439T03001	E439	2022	2021		WHE	Y
F222E01703	F222	2022	2021		BT	Y
A3C1A07837	A3C1	2022	2019		CB	Y
D439T07550	D439	2018	2010		WHE	Y
D439T07564	D439	2018	2010		WHE	Y
D875B02347	D875	2015	2015		CB	Y
E843T01561	E843	2021	2021		WHE	Y
D435T06015	D435	2018	2014		WHE	Y
B875B02026	B875		2005	2014	CB	Y
A7S1A06855	A7S1	2022	2019		CB	Y
K160B17731	K160	2022	2009		CB	Y
G807B08190	G807	2016	2009		CB	Y
E439T02005	E439	2022	2021		WHE	Y
R177B01731	R177	2022	2021		CB	Y
B869T01895	B869	2015	2006		WHE	Y
A917E01551	A917		2006	2007	BT	N
G807B17036	G807		2007	2009	CB	N
B416A01652	B416		2006	2008	CB	N

The last column has been pre-filled out als an example. 

What will be the DAX code to fill the 'For Service' column?

Thanks in advance.

[WLFRD]

Thanks for your help!

[Anonymous · Accepted solution]

Hi @WLFRD ,

Please try to create a new column with below dax formula:

Service Obligation =
VAR _type = [Type]
VAR _year = [Year]
VAR last_built = [Last Built]
VAR cur_year =
    YEAR ( TODAY () )
VAR _a =
    IF ( ISBLANK ( _year ), last_built + 10, _year + 10 )
VAR _b =
    IF ( ISBLANK ( _year ), last_built + 15, _year + 15 )
RETURN
    SWITCH (
        _type,
        "WHE", IF ( _b < cur_year, "Y", "N" ),
        "BT", IF ( _b < cur_year, "Y", "N" ),
        "CB", IF ( _a < cur_year, "Y", "N" )
    )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.