PowerPivot 3 month rolling average

Question

I need some help creating a measure in PowerPivot. I have Googled and tried all the options I could find with success. I have a Fact table with sales leads. Some of the leads gave us a sell some not. I need to measure the value of the leads. I sum the values and divide it with the number of records I have in the table

Average Total of Leads:=calculate(Table1[Sum of Value]/Table1[Count of Lead name])

My problem is to create the measure which give me 3 months rolling average.

I have tried:

Roll3Average:=[Average Value of leads]/CALCULATE(DISTINCTCOUNT(dimdate[MonthName]), 
          DATESINPERIOD(dimdate[Dates],
                        LASTDATE(dimdate[Dates]),-3,Month
                       )
         )

I have tried:

Rolling3Average:=IF(COUNTROWS(VALUES(dimdate[MonthName])) = 1, 
    CALCULATE( 
[Average of Value]/ COUNTROWS(VALUES(dimdate[MonthName] ) )  , 
            DATESBETWEEN( 
                dimdate[Dates], 
                FIRSTDATE(PARALLELPERIOD(dimdate[dates], -2, MONTH)), 
                LASTDATE(PARALLELPERIOD(dimdate[dates], 0, MONTH)) 
            ), ALL(DimDate) 
    ) 
)

I have tried:

Total Sales rolling:=
CALCULATE (averagex(Table1,[Average Total of deals]),
    FILTER (ALL ( dimdate),dimdate[Month] >= MAX (dimdate[Month]) -2&& dimdate[Month] <= MAX ( DimDate[Month])))

I cannot get it right.

I hope someone can see where I go wrong.

@Marcus

Click here and see my datamodel Thanks

I have still troubles with my data mode.

I have linked a very simplified example. I hope someone can help me.

Thank you

Marcus Marcus · Accepted Answer · 2017-11-02T22:27:35

Note in the example I am using, I am using [Sales] instead of leads.

The main structural change you will want to make is to create a month_index in your dimDates table. The advantage of have that field is that it makes calculating the total over 3 months easier, since it removes having to handle cross year like in your 2nd example. The other advantage is having a month_index will handle non standard calendars, e.g. 4-4-5

To start with:

Sales:=SUM(Data[Qty])

The next part would be to calculate the sales(or leads) over the 3 months. The below we are using the month_index field to quickly define the date range in which we want to sum.

3_Month_Sales :=
CALCULATE (
    [Sales],
    FILTER (
        ALL ( dimdate ),
        dimdate[Month_index] <= MAX ( dimdate[Month_index] )
            && dimdate[Month_index]
                >= MAX ( dimdate[Month_index] ) - 2
    )
)

The next part depends on the ask, since the average could be calculated two ways. The main question is it a 3 month average, or is it based on the numerator being the number of months that have a value greater then 0.

The simple way:

3_Month_Average:=DIVIDE( [3_Month_Sales], 3)

The more complex way, in which I learned you can wrap SUMX in a calculate. The idea being the calculate is looking at the period for your 3 months, and then the sumx is iterating down by year then Month. At the end it is checking if sales is greater then 0, if it then 1 is assigned. Then those 1's are summed

Count_of_Periods :=
CALCULATE (
    SUMX (
        VALUES ( dimdate[Year] ),
        SUMX ( VALUES ( dimdate[Month] ), IF ( [Sales] > 0, 1 ) )
    ),
    FILTER (
        ALL ( dimdate ),
        dimdate[Month_index] <= MAX ( dimdate[Month_index] )
            && dimdate[Month_index]
                >= MAX ( dimdate[Month_index] ) - 2
    )
)

And then finally

3_Month_Alternative:=DIVIDE([3_Month_Sales], [Count_of_Periods])

Below would be an image using some random sample data, and how the different fields interact. As part of the example the April 2017 data was removed, to show how the count_of_periods calculation was able to handle the fact there was no data in that period

PowerPivot 3 month rolling average

1 Answers