Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Grouping dates in a matrix

Hello everyone,

 

I've been trying to get my matrix to aggregate data for years prior or later than this year together. While data from this year is broken down to months Like this:

  202220222022202220222022202220222022202220222022 

 

Prior to 2022JanuaryFebruaryMarch AprilMayJune JulyAugustSeptemberOctoberNovemberDecemberlater than 2022
SC22 9111551141145
WA28 300410106497

 

Is this even a possibility?

  • See if this works for you. You need to create a table which has the layout you need. In my example I have a date table, so I am creating the Matrix Layout table referencing the Date Table and adding the Prior and After rows as follows:

    Matrix Layout =
    VAR _Current =
        CALCULATETABLE (
            SUMMARIZE (
                'Date Table',
                'Date Table'[Year],
                'Date Table'[Month],
                'Date Table'[MonthNum]
            ),
            'Date Table'[Year] = YEAR ( TODAY () )
        )
    VAR _otherPeriods =
        { ( "Prior to", YEAR ( TODAY () ), 0 ), ( "After", YEAR ( TODAY () ), 13 ) }
    RETURN
        UNION ( _Current, _otherPeriods )
    

    Once the table is loaded, I'm adding a sorting order for the "Year" column using:

    Sort = 
    SWITCH(
        'Matrix Layout'[MonthNum],
        0,1,
        13, 3,
        2)

    To get:

    The model is as follows:

     

     Sort the "Year" column by "Sort"; Sort the "Month" column by "MonthNum" column.

    Create the following measure (I'm using a simple SUM for the calculations, so use whatever you need)

    Final Measure =
    VAR _Prior =
        CALCULATE (
            [Sum Sales],
            FILTER ( ALL ( 'Date Table' ), 'Date Table'[Year] < YEAR ( TODAY () ) )
        )
    VAR _After =
        CALCULATE (
            [Sum Sales],
            FILTER ( ALL ( 'Date Table' ), 'Date Table'[Year] > YEAR ( TODAY () ) )
        )
    VAR _Current =
        CALCULATE (
            [Sum Sales],
            TREATAS ( VALUES ( 'Matrix Layout'[Month] ), 'Date Table'[Month] ),
            FILTER ( 'Date Table', 'Date Table'[Year] = YEAR ( TODAY () ) )
        )
    RETURN
        SWITCH (
            SELECTEDVALUE ( 'Matrix Layout'[MonthNum] ),
            0, _Prior,
            13, _After,
            _Current
        )
    

    Now create the matrix visual with the fields from the Matrix Layout table, whatever you need as rows and the [Final measure] to get:

     I've attached the sample PBIX file

7 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion

    Does  the year selection need to be dynamic or is it always going to be the current year?

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Paul,

       

      So the current year will always be the one broken down into months. So by the next year 2023 would be broken down and 2022 will be aggregated with the previous years (2021, 2020, etc).

       

      Thanks for trying to help out

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion

        See if this works for you. You need to create a table which has the layout you need. In my example I have a date table, so I am creating the Matrix Layout table referencing the Date Table and adding the Prior and After rows as follows:

        Matrix Layout =
        VAR _Current =
            CALCULATETABLE (
                SUMMARIZE (
                    'Date Table',
                    'Date Table'[Year],
                    'Date Table'[Month],
                    'Date Table'[MonthNum]
                ),
                'Date Table'[Year] = YEAR ( TODAY () )
            )
        VAR _otherPeriods =
            { ( "Prior to", YEAR ( TODAY () ), 0 ), ( "After", YEAR ( TODAY () ), 13 ) }
        RETURN
            UNION ( _Current, _otherPeriods )
        

        Once the table is loaded, I'm adding a sorting order for the "Year" column using:

        Sort = 
        SWITCH(
            'Matrix Layout'[MonthNum],
            0,1,
            13, 3,
            2)

        To get:

        The model is as follows:

         

         Sort the "Year" column by "Sort"; Sort the "Month" column by "MonthNum" column.

        Create the following measure (I'm using a simple SUM for the calculations, so use whatever you need)

        Final Measure =
        VAR _Prior =
            CALCULATE (
                [Sum Sales],
                FILTER ( ALL ( 'Date Table' ), 'Date Table'[Year] < YEAR ( TODAY () ) )
            )
        VAR _After =
            CALCULATE (
                [Sum Sales],
                FILTER ( ALL ( 'Date Table' ), 'Date Table'[Year] > YEAR ( TODAY () ) )
            )
        VAR _Current =
            CALCULATE (
                [Sum Sales],
                TREATAS ( VALUES ( 'Matrix Layout'[Month] ), 'Date Table'[Month] ),
                FILTER ( 'Date Table', 'Date Table'[Year] = YEAR ( TODAY () ) )
            )
        RETURN
            SWITCH (
                SELECTEDVALUE ( 'Matrix Layout'[MonthNum] ),
                0, _Prior,
                13, _After,
                _Current
            )
        

        Now create the matrix visual with the fields from the Matrix Layout table, whatever you need as rows and the [Final measure] to get:

         I've attached the sample PBIX file