topic Re: Matrix data based on Prev year by period in DAX Commands and Tips

Matrix data based on Prev year by period

markcabantog — Tue, 16 Jan 2024 10:15:43 GMT

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

Re: Matrix data based on Prev year by period

Anonymous — Wed, 17 Jan 2024 04:27:56 GMT

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.

Re: Matrix data based on Prev year by period

markcabantog — Wed, 17 Jan 2024 07:00:09 GMT

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

Re: Matrix data based on Prev year by period

Anonymous — Wed, 17 Jan 2024 07:28:42 GMT

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.

Re: Matrix data based on Prev year by period

markcabantog — Wed, 17 Jan 2024 10:35:19 GMT

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