Need help to exclude data

[adnanarain]

Hi,

I have data until May 2019. In the above picture, the x-axis is date difference in months, the legend is year and I am using a measure for values. I want to show the following:

1. For the year 2019, the line chart should only show result till 0 to 5

2. For the year 2018, the line chart should show the result till 0 to 17 (12 months of 2018 and 5 months of 2019)

3. For the year 2017, the line chart should show the result till 0 to 29 (24 months of 2018 and 2017 and 5 months of 2019) and so on.

Thanks in advance for the help.

[CoalesceIsMore · Accepted solution]

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.

[adnanarain]

please anyone who can help

[CoalesceIsMore · Accepted solution]

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.

[adnanarain]

@CoalesceIsMore  Thanks for the reply. for 2019 I can use the max function to get a current year but for 5, the data will change every month so basically when I will add June data then the 5 becomes 6 and I have to change that every month. If 5 can be dynamic then it will be great help

[adnanarain]

@CoalesceIsMore with your logic i have managed to get the desired result but I don't know why it is showing like below: in the format tab the x-axis property set to CATEGORIAL.

but when I change x-axis type property to CONTINOUS it shows like below: can I get x-axis number without skip

[CoalesceIsMore]

To your first question (when you add June data then the 5 becomes 6), I don't think you'll be able to do that unless you either add the actual month number to your dataset, or if you can make an assumption about the latency of the data (e.g. it's always 14 months before today).

On your second question (sort order)... When you select the chart, you should see a ... at the right side of the chart (could be either upper or lower). Select that to ensure your Sort By is being done on your date diff. You may also need to confirm those are formatted as numbers so they don't have leading spaces that could be affecting it.

[adnanarain · Accepted solution]

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

[PaulJ71]

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.

[adnanarain]

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.

[v-lionel-msft]

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.

[adnanarain]

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

[adnanarain]

Thanks for the reply, I will upload soon.