Monthly variation by customer

[brunodsg]

Hi! I have a sales dashboard and I need a table containing a "ranking" with customers with the biggest variations , in quantity of orders and/or sales value (Upsell and downsell), between specific months, for example, between January and March. I already have the general sales variation between one month and the previous one, but I need to specify each customer (out of a total of 300+, most of them are recurring) and also be able to select which months specifically I want to check this variation, but I'm stuck 😖.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

Sales: = 
SUM(sales_fact[sales])

OFFSET function (DAX) - DAX | Microsoft Learn

Sales prev month: = 
CALCULATE (
    SUM ( sales_fact[sales] ),
    OFFSET (
        -1,
        ALL ( calendar_dimension[Year-Month sort], calendar_dimension[Year-Month] ),
        ORDERBY ( calendar_dimension[Year-Month sort], ASC )
    )
)

RANK function (DAX) - DAX | Microsoft Learn

Diff rank: = 
IF (
    NOT ISBLANK ( [Sales prev month:] ) && NOT ISBLANK ( [Sales:] ),
    RANK (
        SKIP,
        ALL ( customer_dimension[customer] ),
        ORDERBY ( [Sales:] - [Sales prev month:], DESC )
    )
)

[brunodsg]

Hi Jihwan_Kim, 
First of all, I would like to thank you so much!
My semantic model is very similar to yours, and your model and DAX worked very well, specially the ranking, but I still need some adjustments:
I want to be able to select witch months (ou even a group) I need to campare, for example:
January 2023 and July 2023
Or a group of months:
Jan + Fev to Mar + Apr
I really don't know how I can make it works.
I'll also include a percentage column, but it's easy.

[Jihwan_Kim]

Hi,

Thank you for your message.

Can you show the slicer that selects the periods based on the users request? It would be great if I can learn more about how your period_comparing_slicer looks like.

Or, please share your sample pbix file's link here, and then I can try to look into it.

Thank you.