Forum Discussion
Column for previous years months
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!
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()
)
1 Reply
- bhanu_gautamSuper User
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()
)