Finding Values that do not exist last year

[Anonymous]

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

[PaulDBrown · Accepted solution]

@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:

[PaulDBrown]

@Anonymous 

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

[Anonymous]

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

Policy Number	Policy Effective Date	Written Premium NEW	Named Insured
POLICY1	1/1/2019	240,781	COMPANY1
POLICY2	1/1/2019	65,649	COMPANY2

[PaulDBrown]

@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?

[Anonymous]

This is returning a 1 for each row.  will it be possible to check if the policy existed  the same date last year, so in the below example it will check for POLICY1 1/1/2019.

Edit: added more rows to match what I have. So in this scenario, I have POLICY1 that renewed for 2020.  but POLICY2 and POLICY3 did not.

Policy Number	Policy Effective Date	Written Premium NEW	Named Insured	Policies not in Last Year
POLICY1	1/1/2019	240,781	COMPANY1	1
POLICY2	1/1/2019	65,649	COMPANY2	1
POLICY1	1/1/2020	44,048	COMPANY1	1
POLICY3	1/1/2019	24,579	COMPANY3	1

[PaulDBrown · Accepted solution]

@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]

@PaulDBrown 

Thank you, i am still new to DAX.  Are you creating a new table with that code? or is it a Column? the IDE is complaining about syntax so i fear i am doing this wrong.

[PaulDBrown]

@Anonymous 

The CALCULATETABLE function does create a new table (with a single column, unless you add further functions), BUT... when included in a measure, you need to wrap it in an aggregator: measures can only return a single value (they can't return a list of values) Hence the COUNTROWS function.
So when you use any table function within a measure (FILTER, CALCULATETABLE, SUMMARIZE, ADDCOLUMNS etc), the table is created virtually -in memory. 

The solution I have submitted in effect is creating two tables virtually (in each of the measures). 
the first VAR creates a table (virtual) which generates a list of values present this year. The second VAR does the same but for last year.

you can then use the functions:

INTERSECT; which compares both virtual tables and ONLY returns values present in both (virtual) tables.

EXCEPT: compares the first table in the expression vs the second table included and returns a list of values which are present in the first table but not in the second. Therefore which table goes first in the expression is relevant, as you can see in the measure submitted.

Finally, the COUNTROWS function counts the values in the virtual list resulting from the INTERSECT/EXCEPT FUNCTIONS to return a single value for the measure.

I hope that's not too confusing...

can you post the measure which is giving you problems and the error message?

[Anonymous]

Wow thank you for this explanation, make sense.

I am using the measure posted with the data tables and fields changed to match my model. 

I may not be understanding where this code needs to be inserted.  I have tried plugging this code into a measure or column from within the table I am working with and I also tried creating a new table.

Also, if i have the table below how would i be able to accomplish the last column?  Essentially we need to identify policies that were renewed and visualize this.

Policy Number	Policy Effective Date	Written Premium NEW	Named Insured	Renewed
POLICY1	1/1/2019	240,781	COMPANY1
POLICY2	1/1/2019	65,649	COMPANY2	No
POLICY1	1/1/2020	44,048	COMPANY1	Yes
POLICY3	1/1/2019	24,579	COMPANY3	No

[PaulDBrown]

@Anonymous
These are measures which you can use in a table or in the filter pane to filter a table to see the values (the examples I posted are either tables. The table with the numbers include the actuals measures; the tables where you only see the values are table visuals which are filtered in the filter pane by the measures.
Does that make sense?

[Anonymous]

ok, i tried creating a new measure in my table and the IDE is complaining about syntax.  Sorry for the back and forth.

[PaulDBrown]

@Anonymous 

Apologies since I didn't adjust my measures. just change the " ; " for " ," in the syntax (my local settings require " ; " as expression seperators).

See if that solves the issue.

[Anonymous]

Thank you, that fixed it!

I was able to return the policy numbers in that query, but how do I get more values from the table to return?

[PaulDBrown]

@Anonymous 

Sorry, I don't understand your last question. Can you elaborate a little?

[Anonymous]

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

Instead of returning COUNTROWS, how do I return all the values of the table so I can report on policy number and date?

[PaulDBrown]

@Anonymous 

To calculate the date when it from the previous year:

Last Effective Date = IF([Policies Not in this Year] >0; 
                         MAX('DataTable'[Policy Effective Date]); 
                            BLANK())

You can for example either create a table and include the measure:

or create a table and use the measure in the Filter Pane:

Does that help?

[Anonymous]

ok I think I am getting closer.  thank you for all your help!

Another wrench was thrown in, we need to report on any policy that did not renew from last year a little different.  If the policy does not exist in the same month of last year, then the policy did not renew.

how would that be done?

[Anonymous · Accepted solution]

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! 

[PaulDBrown]

@Anonymous 

Well done!!

[Anonymous]

Thanks! Now that we have last year vs this year. How would I be able to create a trend. Month over month?

We know the table is calculated based on today. But how do I keep historicals if policies count month over month