cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IamTDR Regular Visitor
Regular Visitor

Top 3 Consecutive Selling Months DAX Help

Morning

I am trying to write a DAX measure to calculate the top 3 consecutive months of sales.  Once I have this calculated, I need to use the answer as my basis for inventory re-order point.  So I need a DAX formula to calculate the all time top 3 consecutive months of sales.  This number will then be used as the re-order point for when inventory fall below this number.

 

I tried this DAX Measure.

 

Consecutive 3 Months = CALCULATE(Sales_Summary[Total Summary Quantity],DATESINPERIOD('Calendar_Date_Tbl'[calendar_date],LASTDATE(Sales_Summary[sale_date]),-3,MONTH))

 

This does seem to work if I place the date in the visual table because with the dates in the table it just calculates the last three selling months.

 

Maybe I need to add a rank to the measure?

Would appreciate any ideas. Thanks in advance.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-danhe-msft Super Contributor
Super Contributor

Re: Top 3 Consecutive Selling Months DAX Help

Hi @IamTDR,

Based on my test, it could work on my side, could you please offer me your data file and also you could download my sample pbix to have a view.

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Anuradha Frequent Visitor
Frequent Visitor

Re: Top 3 Consecutive Selling Months DAX Help

Share some sample data and expected result in excel format

IamTDR Regular Visitor
Regular Visitor

Re: Top 3 Consecutive Selling Months DAX Help

ISBNMonthQuantity_SoldConsecutive_Selling_Months
15719/1/2018                     7,521                                               12,620
15718/1/2018                     3,575                                               11,634
15717/1/2018                     1,524                                               12,934
15716/1/2018                     6,535                                               20,985
15715/1/2018                     4,875                                               21,908
15714/1/2018                     9,575                                               21,598
15713/1/2018                     7,458                                               21,571
15712/1/2018                     4,565                                               15,687
15711/1/2018                     9,548                                               14,700
157112/1/2017                     1,574                                               11,700
157111/1/2017                     3,578                                               10,126
157110/1/2017                     6,548                                                 6,548

 

So in this sample data this product's top consecutive month is 21,908.  So I need that qty amount to later computer than if my inventory falls below 21,908 I will need to flag it to be re-ordered.

v-danhe-msft Super Contributor
Super Contributor

Re: Top 3 Consecutive Selling Months DAX Help

Hi @IamTDR,

Based on my test, you could refer to below steps:

Create a calender table and create the relationship:

Table = CALENDARAUTO()

1.PNG

Create measure:

Measure = CALCULATE(SUM(Table1[Quantity_Sold]),DATESINPERIOD('Table'[Date],MAX('Table'[Date]),-3,MONTH))

Result:

2.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
IamTDR Regular Visitor
Regular Visitor

Re: Top 3 Consecutive Selling Months DAX Help

Thanks for the reply and suggestion.  Yet I am still trying to find a way to just show the top 3 consecutive months of sales.  The method you provided I came up with something similar. Is there a way to just just the one top consecutive month? So from your example, it would only show 21,908? 

 

Overall I am trying to find a method to show top 3 consecutive months of sales for a bunch of different products.  Once I have that information I want to use those numbers as a basis to calculate re-order stock point.

v-danhe-msft Super Contributor
Super Contributor

Re: Top 3 Consecutive Selling Months DAX Help

Hi @IamTDR,

Based on my test, you could create a new formula:

Max Value = IF([Measure]=MAXX(ALL('Table1'),'Table1'[Measure]),[Measure],BLANK())

Result:

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
IamTDR Regular Visitor
Regular Visitor

Re: Top 3 Consecutive Selling Months DAX Help

Thanks again for the help.   I tried adding this measure to my report and only received blank results.  I downloaded your example and added the measure to your report and it worked.  Played around with it a bit and I now believe the issue is that with my actual data I have loads of different ISBNs not just one.  When I added additional ISBNs to the sample data in your report, the measure proceeded to no longer work.

 

In the meantime I am trying to do the calculation now via SQL and then import the results via a table.  Maybe my best case workaround.

v-danhe-msft Super Contributor
Super Contributor

Re: Top 3 Consecutive Selling Months DAX Help

Hi @IamTDR,

Based on my test, it could work on my side, could you please offer me your data file and also you could download my sample pbix to have a view.

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-danhe-msft Super Contributor
Super Contributor

Re: Top 3 Consecutive Selling Months DAX Help

Hi @IamTDR,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
IamTDR Regular Visitor
Regular Visitor

Re: Top 3 Consecutive Selling Months DAX Help

Sorry for the delay in response.  Yes I was finally able to get your method to work!  Thanks again for all the aid.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 42 members 914 guests
Please welcome our newest community members: