Exclude Customers who had sales in one year but not in the other

[Anonymous]

Hello, I would like to create slicer, some kind of filter or measure which will exclude in the calculation Customers who had sales in one month of one year but not in the same month previuos year. For example this Customer did not have sales in 2022 11 and 12 then 2022 11,12 and 2021 11,12 should be excluded from the total.

[Anonymous]

Hello,
In the attached sample data I have two Customers A & B. I want to compare their sales in 2021 and 2022. If Customer did not have sales in any of the months then this month should also be excluded from the other year so I want to compare apple to apple. For example: Customer A: did not have sales in 11 & 12 of 2022 so his total sales in 2021 should be 800euro and in 2022 also 800euro because we are excluding 11 & 12 month. The same rule applies for Customer B, he also should have 800euro sales in each year.

https://teamshimano-my.sharepoint.com/:u:/r/personal/anna_zalewa-goddijn_shimano-eu_com/Documents/De... 

Year	Month	AMT	Customer Name
2021	1	€ 100	A
2021	2	€ 100	A
2021	3	€ 100	A
2021	4	€ 100	A
2021	5	€ 100	A
2021	6	€ 100	A
2021	7	€ 100	A
2021	8	€ 100	A
2021	9	€ 100	A
2021	10	€ 100	A
2021	11	€ 100	A
2021	12	€ 100	A
2022	1	€ 100	A
2022	2	€ 100	A
2022	3	€ 100	A
2022	4	€ 100	A
2022	5	€ 100	A
2022	6	€ 100	A
2022	7	€ 100	A
2022	8	€ 100	A
2022	9	€ 100	A
2022	10	€ 100	A
2021	1	€ 100	B
2021	2	€ 100	B
2021	3	€ 100	B
2021	4	€ 100	B
2021	5	€ 100	B
2021	6	€ 100	B
2021	7	€ 100	B
2021	10	€ 100	B
2021	11	€ 100	B
2021	12	€ 100	B
2022	1	€ 100	B
2022	2	€ 100	B
2022	3	€ 100	B
2022	4	€ 100	B
2022	5	€ 100	B
2022	6	€ 100	B
2022	7	€ 100	B
2022	8	€ 100	B
2022	9	€ 100	B
2022	10	€ 100	B
2022	11	€ 100	B
2022	12	€ 100	B

[lbendlin]

So something like this?

When you say you want to "compare sales" - how should that look like?  Show the adjusted sales per customer and year, or show some YoY percentage? Will there always be two years involved? What if there are three or more years?

[Anonymous]

SO what I want to have: I have slicers Year (2016-2023) and Month and if I select minimum two years and one month I want to exclude Customers who did not have sales in one of those months or of course both months. I want to have TTL and compare apple to apple. So what you calculated is correct, let me check in my report if it works as I want. Thank you for now!

[Anonymous]

Hello, I have applied your formula to my table and have no result: 

AAA = var A=CALCULATETABLE(SUMMARIZE('A_Report Dataset_DSTM_SalesHistory','A_Report Dataset_DSTM_SalesHistory'[Month],"ct",COUNTROWS('A_Report Dataset_DSTM_SalesHistory')),REMOVEFILTERS('A_Report Dataset_DSTM_SalesHistory'[Year])) var B=SELECTCOLUMNS(FILTER(A,[ct]=2),"Month",'A_Report Dataset_DSTM_SalesHistory'[Month]) return CALCULATE(SUM('A_Report Dataset_DSTM_SalesHistory'[Sales Amount Euro]),B)

Could you please step by step explain what this formuls does?

Could it be that it does not work because my table has data for 5 years so the amount of months could be never 2? 

[lbendlin]

Yes, I asked you about this earlier. CT is the count of rows. In order to make the formula work with any number of years you need to do a distinct count of years rather than hard coding it to two.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523