Rolling Average with just the MONTHS

[SteelChampzz]

I have data that shows only Months (NO DATES) and the Amount.

There are stores/company names also in the data (I can not show store information)

I'm trying to create a Rolling Average for the last 11 months (Jan-Nov) in DAX.

I'm a little confused of how I would create this measure.

All help would be appreciated

Thanks everyone

[Mikelytics]

@SteelChampzz  You're welcome and I am happy to help 🙂

[SteelChampzz]

Also How would I filter my store names into the measure?
Sorry for the extra question, just want to get a clear understanding lol

[Mikelytics · Accepted solution]

Hi @SteelChampzz 

Please find below a solution:

Disclaimer: in the following appraoch I might solve your challenge but in genereal I recommend you to get in touch with the following principles:

star schema data model: Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Date Dimension Table: Creating a simple date table in DAX - SQLBI

Typically You would transform the month columns into dates like Nov -> 11/01/20xxx and so on and then you would work with the date dimension to do something liek I describe in the measure below. 

But to answer your question I go on with an approach to your questions. 🙂

Solution:

exemplary table to be rolled up

Create a mapping table in Power Query

Do a merge between the table

Extend the table

Load the data into the data model and then change the sorting settings:

so that the month columns is sort by number instead of month

Please go in the report view and build the following measure:

Measure = 
CALCULATE(
    SUM(Table_1[Amount]),
    FILTER(
        ALL(Table_1),
        Table_1[Number] <= MAX(Table_1[Number])
    )
)

Please put column Month and the measure in a table visual:

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[SteelChampzz]

Hey Mike,

I'm also curious to know if this is a Rolling AVERAGE or Rolling TOTAL measure?

To me, my assumption would think that this is a Rolling total, if the terminology is different it would be great help to get an understanding on this

[SteelChampzz]

Thank you for the help on this, I was stuck for sure.
I appreciate you doing the step-by-step