How to show data for the selected month and previous month in matrix visual

[Anonymous]

Hello!

I have the matrix visual, which should display measures (are placed as rows) calculated for the selected month, the previous month (before selected) and average for the year (year in which the selected month is). Also I have two slicers: one for year and one for month. I'd like matrix values change based on the selection, but I even don't understand where to start. I'll be helpful for all the answers.

The only way I see now is to create a table with the list of measure names (put them as rows), then a table with name of columns ("this month", "previous month", "year average" ), then create one measure using SWITCH for all the cases and put it  as value. But I'm sure here should be more efficient way. Also I need the column for current month be named not "this month", but as the selected one ("January" etc.)

suppose "January" and "2019" are selected. Then the column with current month is called "January", the data for the December 2018 is calculated in the column "Prev month", the average monthly values are calculated for 2019 

[sturlaws · Accepted solution]

Hi @Anonymous ,

you will need a switch construction in your measures, but you can simplify it a little. You have not written anything about your model or provided any sample file or data, so I have created a simple mockup report to demonstrate how it can be done: report.

When all the requirements are in place, your measures could be written like this:

Measure =
VAR _headerValue =
    SELECTEDVALUE ( Headers[sort] )
VAR _monthNum =
    SELECTEDVALUE ( dimDate[MonthNum] )
VAR _year =
    SELECTEDVALUE ( dimDate[year] )
RETURN
    SWITCH (
        TRUE ();
        _headerValue = _monthNum; CALCULATE (
            SUM ( Sales[sales] );
            FILTER (
                ALL ( dimDate );
                dimDate[MonthNum] = _headerValue
                    && dimDate[year] = _year
            )
        );
        _headerValue = 13; [previousMonth];
        _headerValue = 14; [Year average]
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Wow, it's great. I'll try today to use it with my measures and probably I'll return with some questions... But it's exactly what was needed, thank you a lot!