Calculating Running Average for the Current Financial Year

[ashishrj]

Hello folks,

Need your help for calculating the running average for the current financial year. Below is the scenario:

I have 2 tables. 1] Date table  2] Branch Details table 

I have a column with name as Branch which contains branch name. Also there is a flag which indicates whether the branch was active. I wanted to calcuate the Branch activation the explaination for which is provided below:

Branch Activation

– Average percentage of active branch from start of FY till current month

1. Active Branch – Branch with 1 Policy in a month called active for that particular month

Apr - 10 off 200 branches remained active (i.e 5%)  

May – 20 off 200 branches found active (10%)

June – 30 off 200 branches found active (15%)

So, for the period Apr-June or YTD June, Average Monthly Branch activation percentage would be (5+10+15)/3 = 30/3 = 10%

I am able to calculate split w.r.t. month on month (%) using quick calc functionality and by creating a simple measure named as "Branch Active"

But how would I calculate the average uptil current month in that FY. 

I have tried using below references but with no success

PowerPivot Pro

WiseOwl

Thanks in advance 🙂

[Sean]

@ashishrj

This is a Measure I use to show me the average across the months selected (which is the line in the chart)

So whatever Measure you are using to plot the individual columns use it in this formula

to get the line which represents the average across allselected months (hope this makes sense)

MEASURE Avg/Mo =
DIVIDE (
    CALCULATE ( [MEASURE], ALLSELECTED ( 'Calendar' ) ),
    CALCULATE (
        DISTINCTCOUNT ( 'Calendar'[Year-Month] ),
        ALLSELECTED ( 'Calendar' )
    ),
    0
)

This is the result

So regardless if I display all data ( and have to scroll on a very long chart) or narrow down with the Slicers

the line will be the average across only the selected number of months

At least thats how I understood your question

Hope this helps!

Good Luck!

[ashishrj]

Thanks @Sean for your reply but ultimately that does not work around for me. Let me know if there is any other approach for the same.