calculate average from previous 3 months to be used as a variable for every month in the future

[LParker97]

Hi I'm trying to create a measure that will appear as a secondary bar on a bar chart

Currently I have the average completion per person for each month and I want to use the average of the last 3 calender months. This will then be multiplied and be used to show as a flat sequence of bars across the axis as a constant.

i.e. 

Month | Completed | Capacity | Average
----------------------------------------------
Feb      |      10         |         2      |       5

Mar    |      10         |         2      |       5

Apr      |      16        |         2      |      8

May      |      15         |         3      |       5

June      |      10         |         5      |       2

So As of today (17th June), I would like the average of averages from , MAR, APR & JUN = 6

I want to then use this value and times by the latest capacity (June=5) to form a a series of bars with the same height (for a comparison to the data) for months in the future - It may help to know that this is a different date to the month in the table above..

Sounds complicated, happy to explain further

[v-lionel-msft]

Hi @LParker97 ,

Is the [Average] column a calculated column? If yes, you can do like this.

1. Add a [Index] column.

2. Create a measure.

Measure = 
VAR x = 
CALCULATE(
    AVERAGE(Sheet2[Average]),
    FILTER(
        ALL(Sheet2),
        Sheet2[Index] < MAX(Sheet2[Index])  && Sheet2[Index] >= MAX(Sheet2[Index]) -3 
    )
)
RETURN
IF(
    MAX(Sheet2[Index]) <= 3,
    BLANK(),
    x
)

If the [Average] is a measure then the situation will be more complex, you may need to use AVERAGEX() function.

Of course, you can also change the [Average] to a calculated column.

Best regards,
Lionel Chen

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

[LParker97]

Hi @v-lionel-msft 

So the issue is that the table I've listed is actually a derrivative of my data - which is built in the record view. i.e. product 1 with a completed and closure date in seperate columns with a pointer for the capacity of when it was completed.

The Date I want this to feed into is also a different column - the due date.

So essentially I want the columsn to be forward looking based on the due date but calculated from the averages from the completed and closed dates

[v-lionel-msft]

Hi @LParker97 ,

Has your problem been solved?

If not please give me the sample data model, because we can not understand your data model based on text description alone.

Best regards,
Lionel Chen

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

[amitchandak]

@LParker97 , You can try with Date table .

Example

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.