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.
If I was in this scenario I'd probably try and apply those rules to my data source rather than in the report. I've made assumptions that the data is stored in a database and the additional data isn't used somewhere else within your report.
Thank you PaulJ71 for the reply. My data stored in excel file and i am using that data in other visuals so i can not exclude data from my data source. I only want to apply this condition in this visual only.
Hi adnanarain ,
Please show a sample data model.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply, I will upload soon.
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.
