How to create a dynamic rolling average measure?

[Quiny_Harl]

In fact, I have created the dynamic rolling average measure. Here how it works.
We have 2 slicers - one to select the measure we want to visualize and another one to select the 3 month, 6 month or 12 month rolling average for that measure. Both of those measures are visualized on a line chart.

1. We have a switch measure that switches between the measures based on a slicer selection:

Selected Transport Measure =

SWITCH(SELECTEDVALUE(Parameters[Index]),

0, [Measure 1],

1, [Measure 2],

2, [Measure 3],

3, [Measure 4 Percent]

      )

2. We have a 3m, 6m and 12 rolling average measures which calculate the rolling average for the measure selected from the slicer:

3M Rolling Avg =

var LastSelectedDate = MAX(Calendar[Date])

var Period = DATESINPERIOD(Calendar[Date],LastSelectedDate,-3,MONTH)

var Rolling_Avg =

    CALCULATE(

              AVERAGEX(

                        VALUES(Calendar[Year Month]),

                        [Selected Transport Measure]

                      ),

               Period

             )

return Rolling_Avg

3. Then we have another switch measure that switches between 3m, 6m and 12m based on a selection from the other slicer. This is the measure used in the visual.

Selected Rolling Avg Measure =

IF(ISFILTERED('Parameters - Rolling Avg'[MRA Measures]),

                SWITCH(SELECTEDVALUE('Parameters - Rolling Avg'[Index]),

                                    3, [3M Rolling Avg],

                                    6, [6M Rolling Avg],

                                    12, [12M Rolling Avg],

                                    BLANK()

                     ),

    BLANK()

   )        

All works fine, except for Measure 4 Percent (see step 1) because unlike the other 3 measures it is a percent. Is there a way to make this thing work with a measure that's formatted as a percent?

[parry2k · Accepted solution]

@Quiny_Harl simply launch TE 

- create a calculation group

- add 4 calculation item, name those whatever you want

- assuming calculate items are called "Item1", "Item2", "Item3", Item4"

- for item1, it will return [Measure 1] and in the format string give the format string this suppose to have, do the same for each item in the calculation group. For item 4, the format string will be %

Apply and save the changes, To visualize

- put calculation item as a slicer so use can which measure they want to see

- in a matrix visual, put a column on rows, calculation item column on columns, any measure on value

and based on the slicer selection, the respective measures will show in the matrix visual with the respective format.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[parry2k]

@Quiny_Harl instead of using the selected transport measure and switch function, you should use calculate groups instead, and there you can define the different formats for different measures and in measure 4, it will be the percent format. There are many blog posts on how to change format string in calculation group, just google it and you will find it. You can also check my playlist on the calculation group here. https://youtube.com/playlist?list=PLiYSIjh4cEx2s8-jXBhv5aCu5_6F9zQ3I

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[Quiny_Harl]

@parry2k ,thanks for this suggestion! Would you mind explaining a bit more how the calculation groups can be used for this scenario? Or maybe if you have a mock up pbi file where this has been applied?