Year selection by current year and previous year in matrix chart

[prakash11440278]

HI Team,

Can you please provide the dax expressions for the current year & previous year measures by year filter.

Below is the screen shot with requirement.

[Anonymous · Accepted solution]

Hi @prakash11440278 ，

Please correct me if I wrongly understood your question.

You can follow the steps below to achieve your needs.

(1)Create a Year table, and use the table as a slicer.(In visual filter, you can hide 2019)

Year = VALUES(Sales[Date])

(2)Create a measure to return the values between selected year and selected year-1.

Measure = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,MAX(Sales[Date])>=MAX('Year'[Year])-1 && MAX(Sales[Date])<=MAX('Year'[Year])))

(3)Add a matrix visual ,use Sales[Category] as rows, Sales[Date] as columns, Sales[Measure] as values. You will get the data you want .

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

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

[amitchandak]

@prakash11440278 , with help from year/date table joined

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

But if you need to display them as the year in the matrix, use an independent date table

refer this video: https://youtu.be/44fGGmg9fHI

[prakash11440278]

This expressions not working for my scenario and also i am seeing grand total mismatch.