Return value from 3 months ago

[kevinedora]

Hi,

I'm trying to add another column to "Table 1" basically saying what's the latest movement from 3 months ago per account(it should be "installs" only). Table 2 should be the lookup table. Criteria should be the same account no. and item.

I wanted this to be in a matrix, so i'm not sure if calc column should be the correct response or measure maybe? Please advise. Thank you!

Table 1

Period (YYYY-MM-DD)	Account no.	Item
20220301	617326299	2SFEM
20220301	617326299	1SFEM

Table 2

Period (YYYY-MM-DD)	Account no.	Movement	Item
20220102	617326299	Installs	2SFEM
20220103	617326299	Recon	2SFEM
20220104	617326299	Discon	1SFEM

Result

Period (YYYY-MM-DD)	Account no.	Item	Installs 3Months
20220301	617326299	2SFEM	Yes
20220301	617326299	1SFEM	No

[ryan_mayu]

@kevinedora 

not clear about the 3 month ago. it looks like the date of install is only two month ago. could you pls clarify more about this?

maybe you can try below DAX to create a column

Column = 
VAR _install=maxx(FILTER('Table 2','Table 1'[Account no.]='Table 2'[Account no.] &&'Table 1'[Item]='Table 2'[Item] && 'Table 2'[Movement]="installs"),'Table 2'[Movement])
return if(_install="","N","Y")

[kevinedora]

Hello @ryan_mayu 

Thanks for the response. Apologies for the confusion, the example above should be correct since I also count march from the "3 months" ago criteria.

Also, from your calc column, what if the account had been installed in December 2021? the formula would return "Yes". I just need specifically from 3 months ago (January).

In the future, I might use the formula also for the "2 months ago" criteria or "within the month". I hope this clarifies my concern.

[ryan_mayu · Accepted solution]

@kevinedora 

is this what you want

Column =

VAR _install=maxx(FILTER('Table 2','Table 1'[Account no.]='Table 2'[Account no.] &&'Table 1'[Item]='Table 2'[Item] && 'Table 2'[Movement]="installs" && year('Table 1'[Period (YYYY-MM-DD)])=year('Table 2'[Period (YYYY-MM-DD)])&&month('Table 1'[Period (YYYY-MM-DD)])=month('Table 2'[Period (YYYY-MM-DD)])+2),'Table 2'[Movement])

return if(_install="","N","Y")

you can change +2 to +1 or remove it to get 2 month ago or within the month

[kevinedora]

Thanks @ryan_mayu. Can you please send me the sample file? For some reason 'Table 1'year(date) = 'Table 2'year(date) is not working.

[ryan_mayu]

@kevinedora 

pls see the attachment below