Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Finding Values that do not exist last year

Is it possible to find values that do not exist last year, using the date column of the same table?

Need Help
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    6 years ago

    Anonymous 

     

    Policies Renewed this year (present in both):

    Renewed Policies = 
VAR PolTY = CALCULATETABLE(VALUES('DataTable'[Policy Number]);
            FILTER('DataTable';
            YEAR('DataTable'[Policy Effective Date]) = YEAR(TODAY())))
VAR PolLY = CALCULATETABLE(VALUES('DataTable'[Policy Number]);
            FILTER('DataTable';
            YEAR('DataTable'[Policy Effective Date]) = YEAR(TODAY())-1))
Return
COUNTROWS(
    INTERSECT(PolLY; PolTY))

     

    Policies not renewed (present last year but not this year):

    Policies Not in this Year = 
VAR PolTY = CALCULATETABLE(VALUES('DataTable'[Policy Number]);
            FILTER('DataTable';
                YEAR('DataTable'[Policy Effective Date]) = YEAR(TODAY())))
VAR PolLY = CALCULATETABLE(VALUES('DataTable'[Policy Number]);
            FILTER('DataTable';
                YEAR('DataTable'[Policy Effective Date]) = YEAR(TODAY())-1))
Return
COUNTROWS(
    EXCEPT(PolLY; PoltY))

     

    Which gets you this result:

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    I did more research and found that this was able to provide the data i needed.

     

    UnRenewed Policies = 
VAR PolTY = CALCULATETABLE(VALUES('PY WrittenPremium'[Policy Number]),
            FILTER('PY WrittenPremium',
                MONTH('PY WrittenPremium'[Policy Effective Date]) = MONTH(TODAY()) &&
                YEAR('PY WrittenPremium'[Policy Effective Date]) = YEAR(TODAY())           
            ))

VAR PolLY = CALCULATETABLE(VALUES('PY WrittenPremium'[Policy Number]),
            FILTER('PY WrittenPremium',
                MONTH('PY WrittenPremium'[Policy Effective Date]) = MONTH(TODAY()) &&
                YEAR('PY WrittenPremium'[Policy Effective Date]) = YEAR(TODAY())-1
            ))

Return
EXCEPT(PolLY,PolTY)

      Thank you for all your help! 

19 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

     

    The answer is yes...but what values are you trying to look for? Dates (since you mention dates column...)?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I am trying to find if the policy exists last year based off this single table.

       

      Policy Number

      		Policy Effective DateWritten Premium NEWNamed Insured
      POLICY11/1/2019240,781COMPANY1
      POLICY21/1/201965,649COMPANY2
      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous 

         

        Try this to identify policies present this year but not last year:

        Let your table be "Data Table":

         

        Policies not in Last Year = 
VAR PoliciesThisYear = CALCULATETABLE(VALUES(Data Table [Policy Number]),
                      FILTER( Data Table,
                              YEAR(Data Table[Policy Effective Date]) = YEAR(TODAY())))
VAR PoliciesLastYEAR = CALCULATETABLE(VALUES(Data Table [Policy Number]),
                      FILTER( Data Table,
                              YEAR(Data Table[Policy Effective Date]) = YEAR(TODAY())-1))

RETURN
COUNTROWS(
     EXCEPT(PoliciesThisYear, PoliciesLastYear)

         

         

        And add this measure to a table with the Policy Number as a row.

         

        Does it work?