Need help with writing DAX

[Anonymous]

Hello Community,

Can you please help me writing this query in Power BI DAX (Column).

I have table, where I want to find the customer converstions  from first product  to their last product. i.e converstion from Product A to B and B to A.   Also, There is no customers in data that have purchased two products on same day. Just to avoid ambguity, I will exclude the customers that purchased on same day. 
Note -  I want to exclude the products that are purchased on First day of year. 

I would really appreciate if you could help me with it. 

Many Thanks, Chana

[PaulDBrown]

please post sample data (not an image) and a depiction of the expected outcome. Thanks!

[Anonymous]

Hi @PaulDBrown,  Please see below: I need result in column like Product Conversion TEST. 
Note: I want excludes data on First day of  year. 
Thank you in advance,

Asad

Date	ID	Product	TEST COL	Product Conversion TEST
1/1/2020 0:00	6	A	A Only	A2A
1/1/2020 0:00	7	B	B Only	B2B
2/12/2020 0:00	1	A	A Only	A2A
2/13/2020 0:00	6	A	A Only	A2A
3/12/2020 0:00	1	A	A Only	A2A
5/13/2020 0:00	5	B	B Only	B2B
12/5/2020 0:00	5	B	B Only	B2B
1/1/2021 0:00	1	A	A & B	A2B
1/1/2021 0:00	3	A	A Only	A2A
1/2/2021 0:00	2	A	A Only	A2A
1/3/2021 0:00	2	A	A Only	A2A
1/8/2021 0:00	1	B	A & B	A2B
4/14/2021 0:00	4	B	B Only	B2B

[PaulDBrown]

thanks for that. Do you want a measure or a calculated column?

[Anonymous]

Calculate column please. Thanks!

[PaulDBrown]

Sure! Can you please explain the logic behind your Test Col? 
and please clarify the criteria. For example for ID = 1, you have AAAB but you your progression states:

AA

AA

AA

AB

Does that mean the criteria is current date vs. previous?

[Anonymous]

Hi @PaulDBrown ,

Test col  reperesents if customers has only Product A or B or both  based on calendar year. 
 To know the conversion from Product A to B or B to A , I am looking to First and Last date of product purchases. 
Thank you, 

Chana

[PaulDBrown]

So is the calculation by year or progressive irrespective of the year?

[Anonymous]

Calculation by Year. Thank you

[PaulDBrown · Accepted solution]

Try this code for a calculated column:

Product Conversion =
VAR _Table =
    CALCULATETABLE (
        'Table',
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID] ),
            YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
        )
    )
VAR DistVal =
    CALCULATE (
        COUNT ( 'Table'[Product] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID] ),
            YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
        )
    )
RETURN
    IF (
        DistVal = 1,
        'Table'[Product] & "2" & 'Table'[Product],
        CONCATENATEX ( _Table, 'Table'[Product], "2", 'Table'[Date] )
    )

I've added a new row for ID 1 in 2021 so you can see the progression in case you have more than 2 operations in a year

As an alternative you could just list the progression based on the number of Products by year using:

Product Conversion =
VAR _Table =
    CALCULATETABLE (
        'Table',
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID] ),
            YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
        )
    )

RETURN
    CONCATENATEX ( _Table, 'Table'[Product], "2", 'Table'[Date] )

To get: