Forum Discussion

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

 

 

 

Need Help

9 Replies

  • Anuradha's avatar
    Anuradha
    Frequent Visitor
    7 years ago

    Share some sample data and expected result in excel format

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