Forum Discussion

DevLearner0's avatar
DevLearner0
Frequent Visitor
2 years ago
Solved

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 ca...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

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

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

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