Weekly running average

[AG_PBI]

Hi,

I am new to Power BI and this is my first report. I have a dataset as follows:

Loan Number	Submission Week	Volume
Loan1	1	1000
Loan2	1	2000
Loan3	1	2000
Loan4	2	3000
Loan5	2	3000
Loan6	3	1000
Loan7	3	3000
Loan8	3	4000
Loan9	3	2000

In the report dashboard I need to present it like this:

Sub Week	Total Volume	Weekly Running Avg Vol
1	5000	5000
2	6000	5500
3	10000	7000

I refeered a few WINDOW function videos, but not able to get the weekly running average. Please suggest.

[Kedar_Pande]

@AG_PBI 

New Measurs

Total Volume = SUM('YourTableName'[Volume])

Weekly Running Avg Vol = 
AVERAGEX(
DATESINPERIOD(
'YourTableName'[Submission Week],
MAX('YourTableName'[Submission Week]),
-3,
WEEK
),
[Total Volume]
)

This should help you get the desired results in your Power BI report.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Kedar_Pande]

@AG_PBI 

New Measurs

Total Volume = SUM('YourTableName'[Volume])

Weekly Running Avg Vol = 
AVERAGEX(
DATESINPERIOD(
'YourTableName'[Submission Week],
MAX('YourTableName'[Submission Week]),
-3,
WEEK
),
[Total Volume]
)

This should help you get the desired results in your Power BI report.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[AG_PBI · Accepted solution]

I figured it out. In the RunningSum calculation, it should be ALL('submit') instead of ALL('submit'[Submission Week]). Then it worked. Thanks!

[Anonymous]

Hi @AG_PBI ， hello ,rajendraongole1, thank you for your prompt reply!

Congratulations on solving this issue and thanks for sharing your solution.

Please remember to accept your solution as answer.

It will do great help to those who meet the similar question in this forum.

Thanks again for your contribution.

[AG_PBI]

Thanks for your swift feedback. But it is not working as expected. I split the code and found that 'RunningSum' is exactly same as 'Total Volume'. It is just giving the sum of that week itself instead of doing the total with previous week(s).

[rajendraongole1]

Hi @AG_PBI - create a measure for the total weekly volume as below 

Total Volume =
SUM('Table'[Volume])

Now create one more measure to calculates the average of all weekly total volumes up to the current submit week.

Weekly Running Avg Vol =

VAR CurrentWeek = MAX('submit'[Submission Week])

VAR RunningSum =

    CALCULATE(

        SUM('submit'[Volume]),

        FILTER(

            ALL('submit'[Submission Week]),

            'submit'[Submission Week] <= CurrentWeek

        )

    )

VAR WeekCount =

    COUNTROWS(

        FILTER(

            ALL('submit'[Submission Week]),

            'submit'[Submission Week] <= CurrentWeek

        )

    )

RETURN

    DIVIDE(RunningSum, WeekCount)

)
RETURN
DIVIDE(RunningSum, WeekCount)

shared the expected output in above snapshot. 

Hope it works.