Distinct count to match VATs and Years
Hi everyone!
I have a file with companies' VAT Numbers, the Financial Year, and their Net Sales during that year. It looks like this:
| VAT Number | Financial Year | Net Sales |
| 8002 | 2018 | 100000 |
| 8003 | 2018 | 200000 |
| 8004 | 2018 | null |
| 8002 | 2019 | 400000 |
| 8003 | 2019 | 500000 |
| 8004 | 2019 | 600000 |
| 8002 | 2020 | 0 |
| 8003 | 2020 | 800000 |
| 8004 | 2020 | 900000 |
| 8002 | 2021 | 1000000 |
| 8003 | 2021 | 1100000 |
| 8004 | 2021 | 1200000 |
| 8002 | 2022 | 1300000 |
| 8003 | 2022 | 1400000 |
| 8004 | 2022 | 1500000 |
| 8002 | 2023 | 0 |
| 8003 | 2023 | 1700000 |
| 8004 | 2023 | 1800000 |
| 8005 | 2023 | 1900000 |
What I would like to do is create a measure that sums the net sales for the following companies:
(1) Companies whose VAT Number appears in the data as many times as the financial years available (in this case 6 times, but it needs to be dynamic).
-> Therefore the company with VAT 8005 should be excluded.
(2) Companies whose Net Sales are neither null nor 0 in 2023.
-> Therefore, the company with VAT 8002 should be excluded.
The issue that I am facing is that my formula only filters out the companies whose Net Sales are null or 0 during 2023, but IT DOES NOT FILTER OUT COMPANIES WHOSE VAT APPEARS ONLY FEW TIMES (like 8005 in this example). The Distinct Count is not working. There are two DAX measures that I am using but none is working:
(1)
(2)
I would really appreciate your help! Thank you very much in advance!
Distinct count to match VATs and Years