Dynamic distinctcount using a variable data

[anne-sophie]

Hello community!

Here is an issue I keep thinking about without finding the solution...

I have 2 tables :

product

ProductCode	FirstRelease	EndOfProduct
PC_01	02/01/1990
PC_02	01/06/2017
PC_03	01/06/2018
PC_04	01/06/2019
PC_05	01/06/2020
PC_06	01/01/2021
PC_07	02/01/1990
PC_08	02/01/1990	01/03/2018
PC_09	02/01/1990	01/03/2019
PC_10	02/01/1990	01/03/2020
PC_11	02/01/1990	01/01/2021

and years:

Years

2018

2019

2020

2021

And I need to calculate how many products were live for each year (with a fixed date wihtin the year, for example Jan 1st) : for each row in Year table, calculate distinctcount(ProductCode) from Product table where FirstRelease <= 01/01/Year and (EndOfProduct is null or EndOfProduct > 01/01/Year).

I manage to do that in PowerQuery but I wonder if it's possible in DAX, as my product table has many many many rows and multiplying them by the number of years to evaluate is not the best. IRL, my ProductTable also have other columns which are used as filters in the report, that's why the counting needs to be dynamic.

Any help appreciated !

[anne-sophie]

Thank you so much ! It works, and I understand the measure's code, so I've improved my DAX skill 🙂

[DataInsights]

@anne-sophie,

Glad to hear that worked, and that your DAX skills improved. 🙂

[DataInsights · Accepted solution]

@anne-sophie,

Try this measure. No relationship exists between the two tables.

Distinct Product Count =
VAR vYear =
    MAX ( Years[Year] )
VAR vStartDate =
    DATE ( vYear, 1, 1 )
VAR vProductRows =
    FILTER (
        Products,
        vStartDate >= Products[FirstRelease]
            && (
                vStartDate <= Products[EndOfProduct]
                    || ISBLANK ( Products[EndOfProduct] )
            )
    )
VAR vResult =
    CALCULATE ( DISTINCTCOUNT ( Products[ProductCode] ), vProductRows )
RETURN
    vResult