cumulative distinct by month

[Ritaf1983]

I have a star schema, and I need to calculate the cumulative number of active customers each month. A customer can be counted multiple times if they were active in different months. An active customer is one who made a transaction and has a row in the transactions table.

The calculation should cover the last 12 months, with the first month starting in October 2023 (from the 1st to the 7th), and the last month being the current one, up to the latest day.

The cumulative count should sum the active customers month by month.

I need help with Dax formula to achieve it.

PBIX is attached

[ThxAlot]

RT_12M = 
VAR __lymtd =
    CALCULATE(
        [Customers quantity],
        CALCULATETABLE(
            DATEADD( DATESMTD( 'Calendar'[Date] ), -1, YEAR ),
            KEEPFILTERS( 'Calendar'[Date] <= TODAY() )
        )
    )
VAR __11m =
    CALCULATE(
        SUMX( VALUES( 'Calendar'[Year-Month] ), [Customers quantity] ),
        DATESINPERIOD( 'Calendar'[Date], MIN( 'Calendar'[Date] ) - 1, -11, MONTH )
    )
RETURN
    __lymtd + __11m

RT_12M = 
VAR __lymtd =
    CALCULATE(
        [Customers quantity],
        CALCULATETABLE(
            DATEADD( DATESMTD( 'Calendar'[Date] ), -1, YEAR ),
            KEEPFILTERS( 'Calendar'[Date] <= TODAY() )
        )
    )
VAR __11m =
    CALCULATE(
        SUMX( VALUES( 'Calendar'[Year-Month] ), [Customers quantity] ),
        DATESINPERIOD( 'Calendar'[Date], MIN( 'Calendar'[Date] ) - 1, -11, MONTH )
    )
RETURN
    __lymtd + __11m

[Ritaf1983]

Hi @ThxAlot 
Thank you very much for your response. I'm not referring to a rolling 12-month sum, but rather a cumulative count month by month for a 12-month period. Please see my reply to @Jihwan_Kim 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

expected result measure: = 
VAR _t =
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALL ( 'Calendar'[Year-Month], 'Calendar'[Year-Month Code] ),
        ORDERBY ( 'Calendar'[Year-Month Code], ASC )
    )
VAR _bydefinition =
    ADDCOLUMNS (
        _t,
        "@distinctcount", CALCULATE ( COUNTROWS ( DISTINCT ( 'orders'[Customer ID] ) ) )
    )
RETURN
    SUMX ( _bydefinition, [@distinctcount] )

[Ritaf1983]

Hi @Jihwan_Kim 
Thank you very much, for the help. You got it right, except I need it for the last 13 months, not from the start of the calender. Meaning, October 2023 should be 0, and the accumulation should start from there. Your calculation sums correctly, but I can't manage to limit the period.
screenshot from my original file :

I have a dax for standard distinct count like :

Active_customers_12_months =

VAR last_date = 'Last refresh'[Last_closed_day]

VAR first_date = DATE(YEAR(last_date) - 1, MONTH(last_date), 1)

VAR last_day_of_first_month = DAY(last_date)

RETURN

CALCULATE(

    DISTINCTCOUNT('FactSalesDaily'[CustomerKey]),

    FILTER(

        'Calendar',

        'Calendar'[Date] >= first_date &&

        'Calendar'[Date] <= last_date &&

        (

            YEAR('Calendar'[Date]) <> YEAR(first_date) ||

            MONTH('Calendar'[Date]) <> MONTH(first_date) ||

            DAY('Calendar'[Date]) <= last_day_of_first_month

        )

    )

)

and tried to create something similar for the cumulated with your solution :

expected result measure =

VAR last_date = 'Last refresh'[Last_closed_day]

VAR first_date = DATE(YEAR(last_date) - 1, MONTH(last_date), 1) -- Custom start date (e.g., October 2023)

VAR _t =

    WINDOW (

        1, -- Start of window

        ABS, -- Absolute reference from the start

        0, -- No offset

        REL, -- Relative positioning for window

        FILTER(

            ALL ( 'Calendar'[Year-Month], 'Calendar'[Year-Month Code] ),

            MIN('Calendar'[Date]) >= first_date && MAX('Calendar'[Date]) <= last_date -- Use MIN and MAX to aggregate and ensure the custom date range is respected

        ),

        ORDERBY ( 'Calendar'[Year-Month Code], ASC ) -- Order by month

    )

VAR _bydefinition =

    ADDCOLUMNS (

        _t,

        "@distinctcount", CALCULATE ( DISTINCTCOUNT ( 'FactSalesDaily'[CustomerKey] ) ) -- Distinct count of customers within the custom window

    )

RETURN

    SUMX ( _bydefinition, [@distinctcount] ) -- Accumulate distinct counts across the window

But it is not working, the calculation is there except of start / end point

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

Sorry that I don't understand 100%, but, if the condition is to specify the starting period based on the needs, please try something like below.
In the measure, regarding VAR _startingdate, I hope you can try to fix it based on the needs. 

expected result measure: =
VAR _startdate =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 ) //custom start date
VAR _startdatecode =
    MAXX (
        FILTER (
            ALL ( 'Calendar'[Date], 'Calendar'[Year-Month Code] ),
            'Calendar'[Date] = _startdate
        ),
        'Calendar'[Year-Month Code]
    ) //custom yearmonth code
VAR _t =
    FILTER (
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( 'Calendar'[Year-Month], 'Calendar'[Year-Month Code] ),
            ORDERBY ( 'Calendar'[Year-Month Code], ASC )
        ),
        'Calendar'[Year-Month Code] >= _startdatecode
    )
VAR _bydefinition =
    ADDCOLUMNS (
        _t,
        "@distinctcount", CALCULATE ( COUNTROWS ( DISTINCT ( 'orders'[Customer ID] ) ) )
    )
RETURN
    SUMX ( _bydefinition, [@distinctcount] )