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

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