DAX measure to count all based on current year and month selected

[nic_learner]

Hi, I have a table with a date column (dd/mm/yyyy) and an ID column which covers a few years. I am trying to create a measure that counts all IDs of the year selected up to the month selected. 

This is the measure I have which works for year 1 but once I add another year, I starts counting all IDs regardless of the year:

Cumulative ID Selected Year=

CALCULATE(COUNT('TABLE'[ID]),

    FILTER(ALL('Date'),

        'Date'[Date] <= MAX('Date'[Date])

))

Here is an example of the data: if the month/year selected is may-22, the count should be 5 but if I select jan-23, the count should only be 1 as it is a different year.

DATE	ID
1/1/2022	152914
1/2/2022	128964
1/3/2022	137373
1/4/2022	126435
1/5/2022	187769
1/6/2022	181764
1/7/2022	174921
1/8/2022	149012
1/9/2022	144947
1/10/2022	150902
1/11/2022	162587
1/12/2022	155684
1/1/2023	189936

Thanks!

[FreemanZ]

hi @nic_learner 

try to add a year condition like:

Cumulative ID Selected Year=

CALCULATE(

    COUNT('TABLE'[ID]),

    FILTER(

         ALL('Date'),

        'Date'[Date] <= MAX('Date'[Date])

            &&YEAR('Date'[Date] = YEAR(MAX('Date'[Date]))

    )

)

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Cumulative ID Selected Year =
CALCULATE (
    COUNTROWS ( DISTINCT ( 'Table'[ID] ) ),
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALL ( 'Date'[Year], 'Date'[Month-Year], 'Date'[Month-Year sort] ),
        ORDERBY ( 'Date'[Month-Year sort], ASC ),
        KEEP,
        PARTITIONBY ( 'Date'[Year] )
    )
)

[nic_learner]

Hi Jihwan_Kim, that works. Thank you so much!