Flag Items based on condition

[LieneB]

Hi!

Can anyone help me with creating measure to mark/flag items in the table in Power BI.

How to mark with "Additional sales" flag those "Sales ID nr" items that have following conditions in place -

same "Salesperson" sold different "Product" to the same "Client code", within 30 days period...

Data table is bellow , thank you in advance!

Sales ID nr	Salesperson	Client code	Sales date	Product
1	Har	A1	01.01.2023	A
2	Har	A1	09.02.2023	B
3	Har	A1	29.02.2023	B
4	Har	A2	01.01.2023	A
5	Har	A2	09.02.2023	A
6	Har	A2	29.02.2023	B
7	Har	A3	01.01.2023	A
8	Har	A3	20.01.2023	B
9	Har	A3	30.01.2023	C
10	Har	A4	01.01.2023	A
11	Har	A4	01.01.2023	B
12	Har	A4	01.01.2023	C
13	Har	A4	01.01.2023	D
14	Har	A4	20.01.2023	E
15	Eli	A4	20.01.2023	E
23	Eli	A1	01.01.2023	A
24	Eli	A1	02.01.2023	A
25	Eli	A1	03.01.2023	B
26	Eli	A2	04.01.2023	A
27	Eli	A2	05.01.2023	B
28	Eli	A2	06.01.2023	C
29	Eli	A3	07.01.2023	C
30	Eli	A3	08.01.2023	C
31	Eli	A3	09.01.2023	C
32	Eli	A4	10.01.2023	A
33	Eli	A4	11.01.2023	A
34	Eli	A4	12.01.2023	B
35	Eli	A4	13.01.2023	B
36	Eli	A4	14.01.2023	C
37	Vai	A4	15.01.2023	C
45	Vai	A1	01.01.2023	A
46	Vai	A2	02.01.2023	B
47	Vai	A3	03.01.2023	C
48	Vai	A4	04.01.2023	D
49	Vai	A5	05.01.2023	E
50	Vai	A6	06.01.2023	F
51	Vai	A7	07.01.2023	G
52	Vai	A7	08.01.2023	G

[eliasayyy · Accepted solution]

hello @LieneB ,

first you have an error in your data , ther eis no february 29 tin the year 2023 be careful 

please try 

Additional Sales Flag = 
VAR CurrentSalesID = 'Table'[Sales ID nr]
VAR CurrentSalesperson = 'Table'[Salesperson]
VAR CurrentClientCode = 'Table'[Client code]
VAR CurrentProduct = 'Table'[Product]
VAR CurrentSalesDate = 'Table'[Sales date]

RETURN 
IF(
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[Sales ID nr] <> CurrentSalesID &&
            'Table'[Salesperson] = CurrentSalesperson &&
            'Table'[Client code] = CurrentClientCode &&
            'Table'[Product] <> CurrentProduct &&
            DATEDIFF(CurrentSalesDate, 'Table'[Sales date], DAY) >= 0 &&
            DATEDIFF(CurrentSalesDate, 'Table'[Sales date], DAY) <= 30
        )
    ) > 0,
    "Additional sales",
    BLANK()
)

[LieneB]

Thank you so much! It worked for me. But with a minor change:

.........DATEDIFF('Table'[Sale date],CurrentSalesDate, DAY) >= 0 &&

DATEDIFF('Table'[Sale date],CurrentSalesDate, DAY) <= 30.......
But for results to be completely precise, I need to eliminate 2 types of mistakes:
1)If Inital sales and Aditional sales happen on the same date, then only one "Sales ID nr" (doesn't matter which one) has to have the flag (Example - Sales ID nr  "10"  and "11");

2)If Adittional sales of same product happen more than once in the 30 day period, then only first "Sales ID nr" has to have the flag (Example - Sales ID nr  "36"  and "37")