Forum Discussion

IamTDR's avatar
IamTDR
Responsive Resident
7 years ago
Solved

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 Help
Anuradha's avatar
Anuradha
Frequent Visitor
7 years ago

Share some sample data and expected result in excel format

  • IamTDR's avatar
    IamTDR
    Responsive Resident
    7 years ago
    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's avatar
      v-danhe-msft
      Microsoft Employee
      7 years ago

      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:

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

       

      Regards,

      Daniel He

       

      • IamTDR's avatar
        IamTDR
        Responsive Resident
        7 years ago

        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.