Rolling 12. Mo. in a matrix, for EVERY Month

[mkleifgen]

Hello,

I need to create a matrix with Month and Year as the columns, and a distinctcount of customer numbers as the value.

The problem is the distinct count of customer numbers needs to be 12 mo. rolling behind every month.

January 2017 column needs to return distinctcount of customer numbers from February 2016 - January 2017.

March 2018 column needs to return distinctcount of customer numbers from April 2017 - March 2018.

I have a sales data table with customer number, and document date columns.

If you need more information for how anything is set up please let me know.

This can't be as complicated as it seems, I must be missing something.

Thanks!

-Mark

[v-yulgu-msft · Accepted solution]

Hi @mkleifgen,

Please refer to below DAX formulas. Remember to change the data type of [YearMonth] to whole number.

YearMonth = FORMAT('Data Table'[Date],"YYYYMM")

Rank =RANKX ( 'Data Table', 'Data Table'[YearMonth],, ASC, DENSE )

Rolling count =
CALCULATE (
    DISTINCTCOUNT ( 'Data Table'[Customer] ),
    FILTER (
        'Data Table',
        'Data Table'[Rank] <= EARLIER ( 'Data Table'[Rank] )
            && 'Data Table'[Rank]
                >= EARLIER ( 'Data Table'[Rank] ) - 4
    )
)

Best regards,

Yuliana Gu

[mkleifgen]

For future users,

In order to continue to grab the rolling count of customers carrying unique items...

I added 

Rolling count =
CALCULATE (
    DISTINCTCOUNT ( 'Data Table'[Customer] ),
    FILTER (
        'Data Table',
        'Data Table'[Rank] <= EARLIER ( 'Data Table'[Rank] )
            && 'Data Table'[Rank]
                >= EARLIER ( 'Data Table'[Rank] ) - 4
    )
'Data Table'[Item Number] = EARLIER('Data Table'[Item Number])
)

[mkleifgen]

Thank you so much for your help @v-yulgu-msft!

This has helped a ton and taught me a lot!

-Mark