Forum Discussion

elvincplee's avatar
elvincplee
New Member
4 years ago
Solved

DAX for next purchase date

Hi, I'm going to calculate the next purchase date by DAX.   The background is that I have a table with purchase_date, customer, product, qty. I'd like to base on the same customer and product to ca...
Need Help
  • m_alireza's avatar
    m_alireza
    4 years ago

    Hi elvincplee ,

    First, create an index column. 
    You can do this by opening Power Query Editor, go to Add Column tab and click on Index Column. After you do that, close Power Query Editor. 



    Then create a calculated column as below: 

    Next Date = 

CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Index] = EARLIER('Table'[Index],1) +1 && 'Table'[Customer]=EARLIER('Table'[Customer]) && 'Table'[Product]=EARLIER('Table'[Product]
        )))


    Sample output: 

     



m_alireza's avatar
m_alireza
Icon for Solution Specialist rankSolution Specialist
4 years ago

Hi elvincplee ,

First, create an index column. 
You can do this by opening Power Query Editor, go to Add Column tab and click on Index Column. After you do that, close Power Query Editor. 



Then create a calculated column as below: 

Next Date = 

CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Index] = EARLIER('Table'[Index],1) +1 && 'Table'[Customer]=EARLIER('Table'[Customer]) && 'Table'[Product]=EARLIER('Table'[Product]
        )))


Sample output: