Column for previous years months

[DevLearner0]

Hi everyone,

I have a typical date table and another table with the column "Year", another one "Quantity" and a last one called "Sales".

I need to create a matrix visual where the rows you can see in the image below: The month-year selected in a slicer, the data of all past year (Not only december but I can't call it anything else than that), the data of 2 years ago, etc.

I have been able to create a measure that can display it correctly, but measures can't be used as rows in these visuals, so it has to be a column.

The measure I created is

Previous_Years = 
var id_year_SALES = SELECTEDVALUE(SalesTable[ID_YEAR])
var id_year_DATE = SELECTEDVALUE(DIM_CALENDAR[NUM_YEAR])

RETURN
SWITCH(id_year_DATE - id_year_SALES,
0, FORMAT(SELECTEDVALUE(DIM_CALENDAR[DATE]), "MMMM YY"),
1, CONCATENATE("DECEMBER ", id_year_DATE - 1),
2, CONCATENATE("DECEMBER ", id_year_DATE - 2),
3, CONCATENATE("DECEMBER ", id_year_DATE - 3), 
"Error")

I didn't even try the part with "Difference with Dec. Previous Year".

If you need any more detail or explanation feel free to ask.

Thank you!

[bhanu_gautam · Accepted solution]

@DevLearner0 , Create a date table if you do not have

DateTable =
ADDCOLUMNS (
CALENDAR (DATE (2000, 1, 1), DATE (2030, 12, 31)),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"MonthYear", FORMAT ( [Date], "MMM YYYY" )
)

Add calculated columns to your SalesTable to represent the different periods. Here’s how you can do it:

CurrentYear = YEAR(TODAY())

MonthYear = FORMAT(SalesTable[Date], "MMM YYYY")

PreviousYear =
IF (
SalesTable[Year] = CurrentYear - 1,
"DECEMBER " & (CurrentYear - 1),
BLANK()
)

TwoYearsAgo =
IF (
SalesTable[Year] = CurrentYear - 2,
"DECEMBER " & (CurrentYear - 2),
BLANK()
)

ThreeYearsAgo =
IF (
SalesTable[Year] = CurrentYear - 3,
"DECEMBER " & (CurrentYear - 3),
BLANK()
)