cancel
Showing results for
Did you mean:
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
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.

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.
9 REPLIES 9
Frequent Visitor

## Re: Top 3 Consecutive Selling Months DAX Help

Share some sample data and expected result in excel format

Regular Visitor

## Re: Top 3 Consecutive Selling Months DAX Help

 ISBN Month Quantity_Sold Consecutive_Selling_Months 1571 9/1/2018 7,521 12,620 1571 8/1/2018 3,575 11,634 1571 7/1/2018 1,524 12,934 1571 6/1/2018 6,535 20,985 1571 5/1/2018 4,875 21,908 1571 4/1/2018 9,575 21,598 1571 3/1/2018 7,458 21,571 1571 2/1/2018 4,565 15,687 1571 1/1/2018 9,548 14,700 1571 12/1/2017 1,574 11,700 1571 11/1/2017 3,578 10,126 1571 10/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.

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

Create measure:

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

Result:

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

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:

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.
Regular Visitor

## Re: Top 3 Consecutive Selling Months DAX Help

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.

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.

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.
Super Contributor

## Re: Top 3 Consecutive Selling Months DAX Help

Hi @IamTDR,

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

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 42 members 914 guests
Recent signins: