Sales before and after if stand is TRUE

[Ttaylor9870]

Hey All,

I've been stuck on the following problem. I'd like to know the amount before and after a customer receives a stand.

Logic would be sales after STAND is true based on the Posting DATE

See my example table below...

CustomerAmountPosting DateItem NoProduct NameStand

A	1	05/01/2023	TD01	AAA	TRUE
A	2	06/01/2023	RED	BBB	FALSE
A	3	07/01/2023	BLUE	CCC	FALSE
B	1	05/01/2023	TD01	AAA	TRUE
B	2	04/01/2023	RED	BBB	FALSE
B	3	07/01/2023	BLUE	CCC	FALSE
C	1	04/01/2023	TD01	AAA	TRUE
C	2	03/01/2023	RED	BBB	FALSE
C	3	02/01/2023	BLUE	CCC	FALSE

Many Thanks,

Taylor

[v-xiandat-msft]

Hi @Ttaylor9870 ,

Below is my table:

The following DAX might work for you:

Column = 
VAR A =
CALCULATE(
    MAX('Tabelle1'[Date]),
    FILTER(
        ALL(Tabelle1),
        'Tabelle1'[stand] = True && 'Tabelle1'[Customer_Amount] = EARLIER(Tabelle1[Customer_Amount])
    )
)
RETURN
   IF(Tabelle1[stand] = True , 
   'Tabelle1'[Date],
   A
    )

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ttaylor9870]

Hey @v-xiandat-msft,

See the below expected results...

Customer	Amount	Posting Date	Item No	Product Name	Stand	Stand Posting Date  (Expected Output)
A	1	05/01/2023	TD01	AAA	TRUE	05/01/2023
A	2	06/01/2023	RED	BBB	FALSE	05/01/2023
A	3	07/01/2023	BLUE	CCC	FALSE	05/01/2023
B	1	05/01/2023	TD01	AAA	TRUE	05/01/2023
B	2	04/01/2023	RED	BBB	FALSE	05/01/2023
B	3	07/01/2023	BLUE	CCC	FALSE	05/01/2023
C	1	04/01/2023	TD01	AAA	TRUE	04/01/2023
C	2	03/01/2023	RED	BBB	FALSE	04/01/2023
C	3	02/01/2023	BLUE	CCC	FALSE	04/01/2023

The logic would be as follows, DAX or Power Query to....

Go through the table to find customer -> Then find the products the customer has bought -> For that specific product if they bought a stand (Stand = TRUE) for that product return the MAX posting Date.

So there could be a couple products per customer. The above is just a short example.😊

Many Thanks,

Taylor

[v-xiandat-msft]

Hi @Ttaylor9870 ,

Can you send us the expected result graph, thank you

Best Regards

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.