Hellloooo
I have the following DAX formula that counts the number of rows based where the date ‘MMYYDD’ (in my calendar table) falls between the min and max dates (table1).
Count =
COUNTROWS (
FILTER (
‘Table1',
[MMYYDD] >= ' Table1' [MinDate]
&& [MMYYDD] < ' Table1'[MaxDate]
)
)
| Table1 | | |
| AccNum | MinDate | MaxDate |
| ACC5281 | 31/03/2021 | 31/03/2024 |
| ACC5281 | 31/03/2021 | 31/03/2024 |
| ACC5281 | 31/03/2021 | 31/03/2024 |
| ACC5281 | 31/03/2021 | 31/03/2024 |
| ACC5281 | 31/03/2021 | 31/03/2024 |
| ACC5255 | 17/01/2023 | 30/11/2023 |
| ACC5255 | 17/01/2023 | 30/11/2023 |
| ACC5255 | 17/01/2023 | 30/04/2023 |
| ACC5255 | 17/01/2023 | 30/04/2023 |
| ACC5254 | 17/01/2023 | 29/02/2024 |
| ACC5254 | 17/01/2023 | 29/02/2024 |
| ACC5254 | 17/01/2023 | 29/02/2024 |
Unfortunately, Table1 includes duplicate account numbers, and this inflates the figures considerably. Is it possible to amend the above formula so it only counts distinct rows from ‘Table1’ where my AccNum is distinct? I am unable to remove duplicates in PowerQuery as my min/max date calculations are created using DAX in ‘Table1’. This is my end output:-
| Cal1 | |
| MMYYDD | Count |
| 01/11/2021 | 81201 |
| 01/12/2021 | 80930 |
| 01/01/2022 | 80654 |
| 01/02/2022 | 80344 |
| 01/03/2022 | 80166 |
| 01/04/2022 | 79635 |
| 01/05/2022 | 78999 |
| 01/06/2022 | 78366 |
| 01/07/2022 | 77729 |
| 01/08/2022 | 77210 |
| 01/09/2022 | 76604 |
| 01/10/2022 | 75846 |
| 01/11/2022 | 75394 |
Any help massively appreciated!
CF
