DAX Flag column by grouping other rows

[aakif_xec]

Hello community,

I need help in understanding the DAX required based on the logical conditions available.
Consider the data - 

Gain flag = 1 when,
1. Date = first_order_date
2. There should not be any sale from the customer for previous 12 months
3. Once the first condition satisfies, the next 11 months for the same customer should follow the same flag. For instance, if order date = 1/1/2022 and first order date = 1/1/2022, then flag = 1 for this customer untill the order date is less than 1/1/2023.

I'm unable to get the 3rd conditions, facing circular dependency errors.

Any advise will be helpful.   

@rajendraongole1 
@Ritaf1983 

@amitchandak 

@Greg_Deckler 
@Ashish_Mathur 

[Greg_Deckler]

@aakif_xec Any chance you can post the data as text in a table?

[aakif_xec]

Hi @Greg_Deckler,
I have also included the optional output as output 2.
I need to either populate the 1 for 12 months including the current month, or insert order date + 1 year, same date for the 12 months against the respective customer. 
 

Date	first_order_date	Sales	Name	GAIN	Output required 1	Output required 2
1/1/2022	1/1/2022	19999	Customer A	1	1	1/1/2023
2/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
3/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
4/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
5/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
6/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
7/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
8/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
9/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
10/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
11/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
12/1/2022	1/1/2022	19999	Customer A	0	1	1/1/2023
1/1/2023	1/1/2022	19999	Customer A	0	0
2/1/2023	1/1/2022	19999	Customer A	0	0
3/1/2023	1/1/2022	19999	Customer A	0	0
4/1/2023	1/1/2022	19999	Customer A	0	0
5/1/2023	1/1/2022	19999	Customer A	0	0
6/1/2023	1/1/2022	19999	Customer A	0	0
7/1/2023	1/1/2022	19999	Customer A	0	0
8/1/2023	1/1/2022	19999	Customer A	0	0
9/1/2023	1/1/2022	19999	Customer A	0	0
10/1/2023	1/1/2022	19999	Customer A	0	0
11/1/2023	1/1/2022	19999	Customer A	0	0
12/1/2023	1/1/2022	19999	Customer A	0	0
1/1/2024	1/1/2022	19999	Customer A	0	0
2/1/2024	1/1/2022	19999	Customer A	0	0
3/1/2024	1/1/2022	19999	Customer A	0	0
4/1/2024	1/1/2022	19999	Customer A	0	0

[AntrikshSharma]

@aakif_xec You should convert that model into star schema, once you get the first order date you can just convert that date into the start of next year by using DATE ( YEAR ( first_order_date ), 1, 1 ) and then compare the dates as you want.