cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Matrix data based on Prev year by period

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

4 REPLIES 4
Community Support

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.

Helper I

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

Community Support

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.

Helper I

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