Filter date/month satisfy complex condition

[duongan1602]

Hi

I have a table like this: 

Date	Customer	Amount
15-Mar	C	200
1-Apr	A	100
26-May	B	200
18-Jun	C	300
13-Jun	A	100

My problemn is, for each Customer, i need to know which month he bought item and also 2 or 3 months ago, he didn't buy it. If there are 2 or months satisfying same this condition, take the month closest to today. 

Sorry for my bad english. I tried but its very complex. Thanks for any help. 

[v-jingzhang]

Hi @duongan1602 

Currently there is no year in your sample data. To compare dates with today, you need to have Year/Month/Day for date values. 

In addition, how to understand the condition "also 2 or 3 months ago, he didn't buy it"? In the current sample data, customer A and C bought the item twice. Which month should be returned for them? You can provide some examples with dummy data to explain the output result. This can help us understand it better. 

Best Regards,
Community Support Team _ Jing

[Ashish_Mathur]

Hi,

Share some more data and show the expected result.

[SpartaBI]

@duongan1602 I created a sample file with your data and a possible way to solve this: Filter date_month satisfy complex condition 2022-07-10.pbix

      

Showcase Report – Contoso By SpartaBI