Hi, I would like to add a calculated column to my matrix that uses other columns in my matrix.

[Yelyak123]

Hi there, 

I am trying to create a matrix that shows me the number of units purchased of a specific product per year by customer. Here is an example of the raw data I am working with 

Date	Name	Product 1	Product 2	Product 3
06/06/2024	Julie	1	5	0
06/16/2024	Tom	2	0	4

For my matrix, I have date and product as columns and the Name by rows. My matrix is outputted correctly. I would then like to add another column to my matrix that is a calculated field called "in last 12 months" and "sales in last 24 months". Can someone help me do that? 

Thanks 

[Anonymous]

HI @Yelyak123,

You can also take a look at the following blog about create matrix with custom fields if this suitable for your requirement:

Creating a “custom” or “hybrid” matrix in PowerBI 

Regards,

Xiaoxin Sheng

[rajendraongole1 · Accepted solution]

Hi @Yelyak123 - you can try the below calculation , ceate two measure for 12 months and 24 hours as below add it to your matric visualization Values field.

Measure for 12 months:

Sales Last12Months =

CALCULATE(

    SUMX('matr','matr'[Product 1] + 'matr'[Product 2] + 'matr'[Product 3]),

    DATESINPERIOD('matr'[Date], LASTDATE('matr'[Date]), -12, MONTH)

)

Measure for 24 months:

SalesLast24Months =

CALCULATE(

    SUMX(matr,'Matr'[Product 1] + 'Matr'[Product 2] + 'Matr'[Product 3]),

    DATESINPERIOD('Matr'[Date], LASTDATE('Matr'[Date]), -24, MONTH)

)

snapshot of matric visual FYR.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!