Forum Discussion
Bug in my model referenceing two different date tables
Quote DAX - Seems to work:
Quote Policies # = Calculate(
SUMX('Policy Information', 'Policy Information'[Quotes Started]),
Filter (Values ( 'Quote Date'[DateKey] ), 'Quote Date'[DateKey] >= Min ( 'Reporting Date'[DateKey] ) ),
Filter (Values ( 'Quote Date'[DateKey]), 'Quote Date'[DateKey] <= MAX ( 'Reporting Date'[DateKey] ) ))
Sales DAX - Not Working:
Sales Policies # = Calculate(
SUMX('Policy Information', 'Policy Information'[Policies Sold]),
Filter (All ( 'Sale Completed Date'[DateKey] ), 'Sale Completed Date'[DateKey] >= Min ( 'Reporting Date'[DateKey] ) ),
Filter (All ( 'Sale Completed Date'[DateKey]), 'Sale Completed Date'[DateKey] <= MAX ( 'Reporting Date'[DateKey] ) ))
You used ALL function in your policies measure, this function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. And the result still less than you expected result. it would be better if you could provide us some sample data, so that we can make further analysis.
Regards,
Charlie Liao
HI, yes I think putting 'ALL' in was a hail mary attepmt to get the sales to show up. :) I have some sample data I can upload....just need to figure out how to do it.
I'm back, I just don't see how to attach or upload a file, so here is a small sample of the data (not all columns or rows):
| Policy Number | Quote SSR Date | Quote SSR Date Key | Completed SSR Date | Completed SSR Date Key | Quotes Started | Policies Sold | Blended Quote Premium | Blended Quote Commission | Blended Sold Premium | Blended Sold Commission | Uniq Line ID | Uniq Policy ID | Uniq OriginalPolicy ID |
| A105 | 01/31/2016 | 20160131 | 02/12/2016 | 20160212 | 1 | 1 | 556 | 0 | 556 | 111.2 | 65616 | 65616 | 65615 |
| A106 | 01/31/2016 | 20160131 | 03/22/2016 | 20160322 | 1 | 1 | 1611 | 201.38 | 1611 | 201.38 | 69818 | 69817 | 69815 |
| A107 | 06/30/2016 | 20160630 | 06/30/2016 | 20160630 | 1 | 1 | 2108 | 263.5 | 2108 | 263.5 | 338428 | 338279 | 78124 |
| A108 | 02/12/2016 | 20160212 | 02/12/2016 | 20160212 | 1 | 1 | 2572 | 321.5 | 2572 | 321.5 | 196657 | 196546 | 196546 |
| A109 | 01/31/2016 | 20160131 | 02/10/2016 | 20160210 | 1 | 1 | 6423.57 | 562.98 | 2286 | 285.76 | 78399 | 78377 | 78375 |
| A11 | 11/22/2016 | 20161122 | 12/23/2016 | NULL | 1 | 0 | 0 | 0 | 0 | 0 | 1065325 | 1064941 | 1064941 |
| A118 | 01/08/2017 | 20170108 | 01/08/2017 | 20170108 | 1 | 1 | 652 | 130.4 | 652 | 130.4 | 1111814 | 1111373 | 78398 |
| A12 | 05/19/2016 | 20160519 | 05/19/2016 | 20160519 | 1 | 1 | 0 | 0 | 0 | 0 | 212625 | 212504 | 75061 |
| A12 | 11/26/2016 | 20161126 | NULL | NULL | 1 | 0 | 4668 | 0 | 0 | 0 | 1068951 | 1068559 | 1068559 |
| A120 | 08/26/2016 | 20160826 | 08/26/2016 | 20160826 | 1 | 1 | 1109 | 221.8 | 1109 | 221.8 | 364508 | 364314 | 74171 |
| A13 | 09/12/2016 | 20160912 | 09/12/2016 | 20160912 | 1 | 1 | 324 | 64.8 | 324 | 64.8 | 370787 | 370588 | 66350 |
| A13 | 11/24/2016 | 20161124 | 12/15/2016 | NULL | 1 | 0 | 4803 | 0 | 0 | 0 | 1067571 | 1067182 | 1067182 |
| A130 | 04/26/2016 | 20160426 | 04/26/2016 | 20160426 | 1 | 1 | 1077.99 | 0 | 1839 | 229.88 | 207763 | 207644 | 66027 |
| A131 | 01/31/2016 | 20160131 | 01/31/2016 | 20160131 | 1 | 1 | 2191 | 0 | 0 | 0 | 69769 | 69768 | 69767 |
| A132 | 06/09/2017 | 20170609 | 06/09/2017 | 20170609 | 1 | 1 | 639 | 79.88 | 639 | 79.88 | 1773689 | 1772984 | 65866 |