topic Monthly variation by customer in DAX Commands and Tips

Monthly variation by customer

brunodsg — Wed, 27 Mar 2024 21:38:00 GMT

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 😖.

Re: Monthly variation by customer

Jihwan_Kim — Thu, 28 Mar 2024 04:09:08 GMT

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 ) ) )

Re: Monthly variation by customer

brunodsg — Thu, 28 Mar 2024 14:46:36 GMT

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.

Re: Monthly variation by customer

Jihwan_Kim — Fri, 29 Mar 2024 03:37:36 GMT

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.