Forum Discussion

Karthis476's avatar
Karthis476
New Member
2 years ago
Solved

Retrieve Previous Month Data from Date Column

Hi, I have Month Column, Customer ID, Target Date. So I just want retrieve last month Target date for individual customer so I can compare their Target dates revision. Please help me on this.
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    I guess you're missing the month-1 filter.

     

    Anyway, base on your latest data, you can try below expression.

     

     

    Result = CALCULATE(MAX('Table'[Target Date]),ALLEXCEPT('Table','Table'[ID]),PARALLELPERIOD('Table'[Month],-1,MONTH))

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

     

xifeng_L's avatar
xifeng_L
Super User
2 years ago

Hi Karthis476 

 

You can refer to below demo.

 

 

Target Date of Last Month = CALCULATE(MAX('Table'[Target Date]),ALLEXCEPT('Table','Table'[Customer ID]),'Table'[Month Index]=EARLIER('Table'[Month Index])-1)

 

The key is to add an index field to the month so that we can use the current index -1 to get the previous month.

 

Demo - Retrieve Previous Month Data from Date Column.pbix

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

 

  • BIstvan's avatar
    BIstvan
    Resolver I
    2 years ago

    xifeng_L  Brilliant solution. You are applying the same method as I am using when it comes to comparing current versus previous. period.

  • Karthis476's avatar
    Karthis476
    New Member
    2 years ago

    Thank you for your response xifeng.

    I tried it your approach however I am getting Maximum recorded date in the target column. 

    I am looking like below. It should retrieve exact previous month Target for each id.

    Please help on this. Thank you 😊 

     

    • xifeng_L's avatar
      xifeng_L
      Super User
      2 years ago

      I guess you're missing the month-1 filter.

       

      Anyway, base on your latest data, you can try below expression.

       

       

      Result = CALCULATE(MAX('Table'[Target Date]),ALLEXCEPT('Table','Table'[ID]),PARALLELPERIOD('Table'[Month],-1,MONTH))

       

       

      Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

       

      Thank you~

       

      • Karthis476's avatar
        Karthis476
        New Member
        2 years ago

        It's working 🥳

        Thank you so much xifeng.. you're a ⭐.