Forum Discussion

IamTDR's avatar
IamTDR
Icon for Responsive Resident rankResponsive Resident
8 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
8 years ago

Share some sample data and expected result in excel format

IamTDR's avatar
IamTDR
Icon for Responsive Resident rankResponsive Resident
8 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
    Icon for Microsoft Employee rankMicrosoft Employee
    8 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
      Icon for Responsive Resident rankResponsive Resident
      8 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.

      • v-danhe-msft's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        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