DAX query to display only Top5 products based on sales in 6 months compared to prior six months

[Anonymous]

Hello,
I have data of 24 months rolling. Table is having Product, Date& Sales columns.
I'm trying to display Only Top5 products based on sales in 6 months compared to prior six months and six months compared to same time in prior year (for seasonality).

Can anyone please help me to acheive this calculation.

We have to show only product output in table view.

Reagrds.

NR

[Anonymous]

Thanks to all for your time and effort, I got the solution.

[v-deddai1-msft]

Hi @Anonymous ，

Would you please try to accept useful reply as answer to help others with the same issue find it more quickly?

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @Anonymous ,

Would you please try to create a  date table in your data model .You can use the following measure to calculate value for 6 months,prior 6 months, same time in prior year "

sales for 6 months =
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -6, MONTH )
)

sales for prior 6 months =
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    DATESINPERIOD ( 'Date'[Date], DATEADD ( 'Date'[Date], -6, MONTH ), -6, MONTH )
)

sales for last year = CALCULATE (
    SUM ( Sales[Sales Amount] ),
    SAMEPERIODLASTYEAR(DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -6, MONTH ))
)

But would you please explain more about how to compare them to sort the products?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

please check the below picture if it is what you are looking for.

I created a dim-date-table with the column that shows first half or second half of the year.

all measures are in the pbix file, link to the below.

https://www.dropbox.com/s/exbdq55cosofrro/nareshkumar.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Hey Jihwan,
This not what I'm looking for, please find the below thread for sample data.
we have to show top5 products by sales in six months without applying any outside date filters.
I'm sure we can acheive this by DAX calculations.
can you help me to get this done.
Thank you.

[selimovd]

Hey @Anonymous ,

this should be possible with the functions RANKX, DATESBETWEEN and DATEADD.

Can you provide a sample data model or a sample table? Then I can give you an approach.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

Hi Denis,
Thank you for your quick responce,
Please find one week sample data, Like this we have the data for last 24 months starting today.

Regards,

NR