DAX expression to find the previous all customers count from the current selection date

[Rathod]

Hi All,

Can anyone help me to get the DAX expression to find the Distinctcount of customers for the past years from the current selection year.

Example : I have customer table with, customer id and year .

Customer ID | Year
11 - 2020
22 - 2020
33 - 2020
11 - 2020
22 - 2020
44 - 2020
77 - 2021
11 - 2021
88 - 2021
99 - 2021
100 - 2022
22 - 2022
101 - 2022
102 - 2023
103 - 2023

Now, if i select 2022 year in slicer o/p should get : 9

[Rathod]

Hi, @Anonymous 

Thanks  for your DAX expression, but o/p should get 9 not 8, anyway i found the expression below.

Customer Count =
VAR maxYear = MAX( 'Table'[Year] )
RETURN
CALCULATE(
DISTINCTCOUNT( 'Table'[Customer ID] ),
'Table'[Year] <= maxYear
)

[Anonymous]

Hi @Rathod ,

Please try to create a measure with below dax formula:

Measure =
VAR _year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Year] < _year )
VAR tmp1 =
    SUMMARIZE ( tmp, [Year], [Customer ID] )
RETURN
    COUNTROWS ( tmp1 )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.