How to visualize same month last year in matrix

[Anonymous]

Hi all,

I want a matrix table showing the sales for the selected month and the same month last year.

I´ve created a formula that works fine:

Sales (-1 Year) = 0-CALCULATE(SUM(transactions[amount]),FILTER(Accountnumber,Accountnumber[Account Group 3] = "Revenue"), SAMEPERIODLASTYEAR(DATES[Date]))

Then I´ve a table that looks like this:

	apr-22
Sales	500
Sales (-1 Year)	2 50

My goal is a table that looks like this instead. 

	apr-22	apr-21
Sales	500	250

Cant figure it out, I think maybe a switch formula? But the problem is that I want the dates also to be visualised so the user now which period they are looking at.

Regards,

Niclas

[Anonymous]

That table you posted would be good enough! How do I create it? 

[JirkaZ · Accepted solution]

Already replied - drop Year and Month on columns and the two measures to the values of a matrix visual

[Anonymous]

Edit: Solved it, needed to the date to be in hierarchy. I guess if I want the same layout as a proper P&L I will need to build it through a P&L logic?

[Anonymous]

That display would be good enough! How do I create that table? 

[JirkaZ]

Drop Year and Month dimensions into the columns of a matrix and the two measures into the values section of a matrix visual

[JirkaZ]

The way this is usually done is that you have 2 measures set up - base and SPLY (Same Period Last Year).

Base: SUM(SalesTable[SalesAmount])
SPLY: CALCULATE([Base], SAMEPERIODLASTYEAR(DimDate[Date]))

Then you just throw your both measures in the matrix while having the date dimension data on columns (for example).

[BanuTK]

This is great JirkaZ. Can you give me the same measure based on last year date but this time day of week aligned date instead of using calendar date alinged function of sameperiodlastyear? So when I choose 24 Apr 2025 in the row dimension of the matrix visual, it will show the measure based on 25 Apr 2024 data instead of 24 Apr 2024 data in the columns of the matrix. Measure name can be SPLY_dowaligned. 
Many thanks in advance.

[Anonymous]

Thanks for you reply! 

Isnt that what I already have? I can see the the revenue from the 2 measures but the Column only show April 2022, not April 2021.

[JirkaZ]

But that is correct. You have the definition almost OK - it's just that in the SPLY calculation you should reference the base measure instead of defining the calculation for the base measure again.

The Date dimension column will always only show the current date and you should name the SPLY measure the way that it's understandable that it's SPLY (you have that already too) 🙂

[Anonymous]

I used the reference as base in the new measure!  But I´m still stuck with the visual problem in the matrix. 

	apr-22
Sales	500
SPLY	250

Instead of:

	apr-22	SPLY
Sales	500	250

[JirkaZ]

That is a correct behavior and there's no way around that

[Anonymous]

Okay, thanks for your time! But there must be some other way to build this, using some other technique?

[JirkaZ]

AFAIK there's no other way. You can't make column header names dynamic. 

This is an correct display of the measures in the table.