Forum Discussion
Helper IMatrix 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
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.
markcabantogHi 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
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.
