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.