Forum Discussion
Need help to exclude data
I'd suggest adding a column to your table such as this, then filter your chart to "Yes":
DisplayOnlyValidPeriods = IF(Table1[Date Difference] <= (2019 - Table1[Account Open Year]) * 12 + 5, "Yes", "No" )Presumably, this is a data set that will be occasionally updated. As such, you'll probably want to update the hard-coded 2019... logic to something that uses the current year/month minus whatever lag you want to include in the reporting.
CoalesceIsMore Thank you so much for your answer. I have managed to get the desired result by using your calculated column but I have amended a little:
DisplayOnlyValidPeriods = IF('Main Query'[z. Date Difference From Account Open to Month] <= (Year(Max('Main Query'[Month_Start_Date]))-1 - 'Main Query'[Account_Open_Date - Year Only]) * 12 + 'Main Query'[Max month], "Yes", "No" )now I am planning to add a calculated table which will only store max month from my date.
Thanks for all the help.
Hi adnanarain ,
Please show a sample data model.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-lionel-msft below is the data I am using. I am uploading summarized data with measures:
Number of accounts is a simple count of account Numbers
| Date Difference | Account Open Year | Number of Accts for Attrition | Attrition Calculation % |
| 0 | 2018 | 1229 | 0.00% |
| 1 | 2018 | 1224 | 0.41% |
| 2 | 2018 | 1224 | 0.41% |
| 3 | 2018 | 1210 | 1.55% |
| 4 | 2018 | 1187 | 3.42% |
| 5 | 2018 | 1172 | 4.64% |
| 6 | 2018 | 1174 | 4.48% |
| 7 | 2018 | 1159 | 5.70% |
| 8 | 2018 | 1156 | 5.94% |
| 9 | 2018 | 1142 | 7.08% |
| 10 | 2018 | 1135 | 7.65% |
| 11 | 2018 | 1140 | 7.24% |
| 12 | 2018 | 1130 | 8.06% |
| 13 | 2018 | 1130 | 8.06% |
| 14 | 2018 | 1126 | 8.38% |
| 15 | 2018 | 1122 | 8.71% |
| 16 | 2018 | 1112 | 9.52% |
| 17 | 2018 | 1105 | 10.09% |
| 18 | 2018 | 1035 | 15.79% |
| 19 | 2018 | 937 | 23.76% |
| 20 | 2018 | 834 | 32.14% |
| 21 | 2018 | 724 | 41.09% |
| 22 | 2018 | 627 | 48.98% |
| 23 | 2018 | 522 | 57.53% |
| 24 | 2018 | 429 | 65.09% |
| 25 | 2018 | 323 | 73.72% |
| 26 | 2018 | 231 | 81.20% |
| 27 | 2018 | 132 | 89.26% |
| 28 | 2018 | 69 | 94.39% |
| 0 | 2019 | 1378 | 0.00% |
| 1 | 2019 | 1374 | 0.29% |
| 2 | 2019 | 1366 | 0.87% |
| 3 | 2019 | 1350 | 2.03% |
| 4 | 2019 | 1328 | 3.63% |
| 5 | 2019 | 1309 | 5.01% |
| 6 | 2019 | 1214 | 11.90% |
| 7 | 2019 | 1110 | 19.45% |
| 8 | 2019 | 949 | 31.13% |
| 9 | 2019 | 840 | 39.04% |
| 10 | 2019 | 705 | 48.84% |
| 11 | 2019 | 603 | 56.24% |
| 12 | 2019 | 484 | 64.88% |
| 13 | 2019 | 371 | 73.08% |
| 14 | 2019 | 277 | 79.90% |
| 15 | 2019 | 191 | 86.14% |
| 16 | 2019 | 101 | 92.67% |
I am using the following measures:
Number of Accounts = COUNTA('Main Query'[Account_Number])Attrition Difference # =
var _lastrow=CALCULATE([Number of Accts for Attrition],FILTER(ALL('Main Query'[Date Difference]),'Main Query'[Date Difference]=0))
return
IF(ISBLANK(_lastrow),0,_lastrow-[Number of Accts for Attrition])Attrition Calculation % = var _lastrow=CALCULATE([Number of Accts for Attrition],FILTER(ALL('Main Query'[Date Difference]),'Main Query'[Date Difference]=0))
return
if(DIVIDE([Attrition Difference #],_lastrow)=1, BLANK(), DIVIDE([Attrition Difference #],_lastrow))
I hope it is clear now, let me know if anything needed.