topic Re: DAX Flag column by grouping other rows in DAX Commands and Tips

DAX Flag column by grouping other rows

aakif_xec — Tue, 18 Jun 2024 11:05:22 GMT

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

Re: DAX Flag column by grouping other rows

AntrikshSharma — Tue, 18 Jun 2024 12:15:11 GMT

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.

Re: DAX Flag column by grouping other rows

Greg_Deckler — Tue, 18 Jun 2024 13:12:34 GMT

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

Re: DAX Flag column by grouping other rows

aakif_xec — Tue, 18 Jun 2024 15:40:31 GMT

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