Matrix Visual Assistance: To Show a Combination of Years and Specific Months

[asa70]

Hi all,

I hope you are all well,

I have been tasked to showcase the 12 month rolling averages for various departments. I have managed to get the calculations for the rolling averages. This is how they want the visual to represent:

Any ideas on how to achieve this view on the matrix visual? Any help would be appreciated 🙂

Kind Regards,

Asa

[v-vpabbu]

Hi @asa70,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @asa70,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @asa70,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[asa70]

Hi @v-vpabbu, apologies for the late reply. The solution is providing blanks which is not the desired outcome

[asa70]

Hi @v-vpabbu, thank you so much for the assistance. Unfortunately, the solution had 2 problems which I saw:

1. The year totals were no longer shown, just the months for the years
2. The solution returns 0 for the selected months

Kind Regards,

Asa

[v-vpabbu]

Hi @asa70,

The missing year totals happen because the measure only calculates when Month is in scope.

Adjust the measure to handle totals separately and verify the underlying data.

Regards,

Vinay

[amitchandak]

@asa70 ,
You need to have measure like the ones below. The year logic can be based on today

Net at Year Level = if( ISINSCOPE('Date'[Month No]) && Max('Date'[Year]) < 2020 , BLANK(), [Net])

[asa70]

Hi @amitchandak , thank you so much for your solution. I have tested it out. Unfortunately, the totals column gives the wrong values to the rolling average calculation

Let me expand on the image, hopefully it can provide more clarity:

1. Rolling Average Column name - This is a part of a measure select function. 
2. The year

3. The last 3 months of the last year

Would I need to make provision for the measure select attribute name in (1) in the measure you provided?

[v-vpabbu]

Hi @asa70,

Try this:

Rolling 12M Avg (Final) = 
VAR _IsMonth = ISINSCOPE('Date'[Month No])
VAR _LastDate = MAX('Date'[Date])
VAR _CutoffDate = EOMONTH(TODAY(), -14)  -- Allows last 12 months + last 3 of previous year

RETURN
IF(
    _IsMonth && _LastDate >= _CutoffDate,
    CALCULATE(
        AVERAGEX(
            DATESINPERIOD('Date'[Date], _LastDate, -12, MONTH),
            [Net]
        )
    ),
    BLANK()
)

Regards,

Vinay Pabbu