rank in summarize to filter last 6 year-month

[piotr_gor]

Hi,

I want to create summary by CustomerId and last 6 Year-Month sales to check if customer if frequent buyer or not. Then I'll just COUNTROWS of that table and I'll get result. But it isn't that easy. 

Code I wrote is following: 

ADDCOLUMNS (
        FILTER (
            CROSSJOIN (
                SUMMARIZE (
                    'Sales',
                    'Sales'[ClientID],
                    Clients[ClientType]
                ),
                SUMMARIZE ( 'Sales', Date[Year-Month] )
            ),
            Clients[ClientType] = "Shop" && "Somehow filter last 6 Year-Month" 
        ),
        "Sales", [Sales]
    )

I've got no idea how to filter last 6 Year-Month in that code. Is there someone who can help?

[v-cgao-msft]

Hi @piotr_gor ,

Please try to get the maximum month and store it in the variable:

VAR MaxYearMonth = MAX ( Date[Year-Month] )

Then pass the variable:

ADDCOLUMNS (
    FILTER (
        CROSSJOIN (
            SUMMARIZE (
                'Sales',
                'Sales'[ClientID],
                Clients[ClientType]
            ),
            SUMMARIZE ( 'Sales', Date[Year-Month] )
        ),
        Clients[ClientType] = "Shop" &&
        Date[Year-Month] > MaxYearMonth - 6
    ),
    "Sales", [Sales]
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum