Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
WLFRD
Helper III
Helper III

Service obligation based on built year of last built

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:

Capture19.JPG

Table 1Table 1Table 2Table 3Table 3  
Serial NumberUnitYearFirst BuiltLast BuiltTypeFor Service?
c849t04433 c849 20022013WHEY
D849T05073 D84920182014 WHEY
c849t04433 c849 20022013WHEY
K007E02051 K007201420122018BTY
A3C1A05384 A3C120202019 CBY
C456T06364 C456201620092016WHEY
E444T04119 E44420192011 WHEY
B214E01744 B214200920062011BTY
B214E01744 B214200920062011BTY
D222E01521 D222201520142019BTY
P177B11112 P17720202015 CBY
B214E01790 B214201020062011BTY
F222E01723 F22220222021 BTY
E439T03001 E43920222021 WHEY
F222E01703 F22220222021 BTY
A3C1A07837 A3C120222019 CBY
D439T07550 D43920182010 WHEY
D439T07564 D43920182010 WHEY
D875B02347 D87520152015 CBY
E843T01561 E84320212021 WHEY
D435T06015 D43520182014 WHEY
B875B02026 B875 20052014CBY
A7S1A06855 A7S120222019 CBY
K160B17731 K16020222009 CBY
G807B08190 G80720162009 CBY
E439T02005 E43920222021 WHEY
R177B01731 R17720222021 CBY
B869T01895 B86920152006 WHEY
A917E01551 A917 20062007BTN
G807B17036 G807 20072009CBN
B416A01652 B416 20062008CBN

 

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.

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

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" )
    )

vbinbinyumsft_0-1678069046862.png

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.

View solution in original post

2 REPLIES 2
WLFRD
Helper III
Helper III

Thanks for your help!

v-binbinyu-msft
Community Support
Community Support

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" )
    )

vbinbinyumsft_0-1678069046862.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors