Matrix data based on Prev year by period

[markcabantog]

Hi all,

I have a Date table that has a "Period". For 2022, Period 1 is Jan 2-Jan29 and in 2023 Period 1 is Jan 1-Jan 28 and so on..

Period	Date
1	01-Jan-23
.	.
.	.
1	28-Jan-23
1	02-Jan-22
.	.
.	.
1	29-Jan-22

my Data table example is

Date	Item	amount
01-Jan-23	Item 1	1
02-Jan-23	Item 1	1
01-Jan-22	Item 1	1
02-Jan-22	Item 1	1

in my dashboard i want to have 2 matrix current year (2023) and prev year(2022)

Current Yr Matrix (2023)

Item	Period 1	Period 2
Item 1	2

for Prev year Matrix I use the dax

measure = CALCULATE ( SUM(Data[amount]), FILTER (ALL ( 'data' ), 'data'[Year] = MAX ( 'data'[Year] ) -1))

Prev Yr (2022)

Item	Period 1	Period 2
Item 1	2

result shows item 1 amount is 2 in Period 1 but the correct amount should be 1 not 2.

can anyone help me with this

[Anonymous]

Hi @markcabantog 

Based on your needs, I have created the following table.

Then you need to establish a one-to-one relationship between the date columns of the two tables in the model view

then you can use the following dax to get the result you want

Measure = CALCULATE ( SUM('Table'[amount]), FILTER (ALL ( 'Table (2)' ), 'Table (2)'[Date].[Year]= MAX('Table (2)'[Date].[Year])-1))

Best Regards,

Jayleny

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

[markcabantog]

Hi Jayleny,

It cannot be 1-1 becuase in table 1 there will be a lot of data that as same date....this is just a sample data that i shared

[Anonymous]

Hi @markcabantog 

The same applies to one-to-many relationships.

I have created a new table, where the total amount of 1/1/2022 is 3, the total amount of 1/2/2022 is 2, and you can see that the total amount of the matrix is 2.

Best Regards,

Jayleny

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

[markcabantog]

it shows Amount is 5 for both period 1 and period 2 in previous yr

period 1 should be 2 and period 2 should be 3