Retrieve Previous Month Data from Date Column

[Karthis476]

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]

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~

[Karthis476]

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 · Accepted solution]

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]

It's working 🥳

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

[Karthis476]

Hi Xileng,

I just one more help from you. In the above example I have a customer id column. Suppose if I get a new I'd then this formula should reflect blank in result column. Can you able to help me on this please?

The above example is reflecting current month top most date for new id instead of blank.

[xifeng_L]

Sorry, I don't quite understand what you mean. Can you explain it in more detail with pictures?

[BIstvan]

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