Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
markcabantog
Helper I
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..

PeriodDate
101-Jan-23
..
..
128-Jan-23
102-Jan-22
..
..
129-Jan-22

 

my Data table example is

DateItemamount
01-Jan-23Item 11
02-Jan-23Item 11
01-Jan-22Item 11
02-Jan-22Item 11

 

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

Current Yr Matrix (2023)

ItemPeriod 1Period 2
Item 12 

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)

ItemPeriod 1Period 2
Item 12 

 

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
v-jialongy-msft
Community Support
Community Support

Hi @markcabantog 

 

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

vjialongymsft_0-1705465152401.png

vjialongymsft_1-1705465165805.png

 

 

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

vjialongymsft_2-1705465411457.png

 

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))

vjialongymsft_3-1705465616667.png

 

 

 

Best Regards,

Jayleny

 

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

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

 

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.

 

vjialongymsft_0-1705476238728.png

vjialongymsft_1-1705476257670.png

 

vjialongymsft_2-1705476289335.png

 

 

 

 

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_1-1705487496942.png

markcabantog_2-1705487521261.png

 

 

 

 

markcabantog_0-1705487473223.png

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

 

 

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors