topic Re: Retrieve Previous Month Data from Date Column in DAX Commands and Tips

Retrieve Previous Month Data from Date Column

Karthis476 — Sun, 09 Jun 2024 08:41:23 GMT

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.

Re: Retrieve Previous Month Data from Date Column

xifeng_L — Sun, 09 Jun 2024 16:06:43 GMT

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~

Re: Retrieve Previous Month Data from Date Column

BIstvan — Sun, 09 Jun 2024 17:59:45 GMT

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

Re: Retrieve Previous Month Data from Date Column

Karthis476 — Sun, 09 Jun 2024 19:08:47 GMT

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 😊

Re: Retrieve Previous Month Data from Date Column

xifeng_L — Mon, 10 Jun 2024 02:12:08 GMT

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~

Re: Retrieve Previous Month Data from Date Column

Karthis476 — Mon, 10 Jun 2024 04:12:41 GMT

It's working 🥳

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

Re: Retrieve Previous Month Data from Date Column

Karthis476 — Tue, 18 Jun 2024 10:22:43 GMT

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.

Re: Retrieve Previous Month Data from Date Column

xifeng_L — Tue, 18 Jun 2024 14:28:24 GMT

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