Forum Discussion
Finding Values that do not exist last year
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:
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!
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 |
Community ChampionAnonymous
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:
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?
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 RenewedPOLICY1 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