Average per month based on total sales for financial year

[lennox25]

Hi, 

Im trying to work out a calculation for the average of total sales per financial year. The average for each month will be the average for the whole year. Any ideas?

Also another  requirement is that this average also rolls on to the months following the financial year? Is this even possible?

Here is my excel table - 

[lennox25]

Perfect! Thank you @SamWiseOwl 

[SamWiseOwl]

Hi @lennox25 

If you aren't using CalendarTables I strongly recommend you look into them:
https://www.wiseowl.co.uk/blog/s2947/calendarauto-table.htm

They make this kind of calculation much easier.

If you don't want to or can't here is a measure based on your information, PBI converted my Month Year to a date column:

Year average =

Var InitialDate =

If(

    month(SELECTEDVALUE('month avg'[Month Year])) >= 4

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year])),04,01)

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year]))-1,04,01)

)

Var FinalDate =

If(

    month(SELECTEDVALUE('month avg'[Month Year])) >= 4

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year]))+1,03,31)

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year])),03,31))

var Final =

CALCULATE(

    AVERAGE('month avg'[Total Sales Per Month])

    ,ALLSELECTED('month avg'[Month Year])

    ,DATESBETWEEN(

        'month avg'[Month Year]

        ,InitialDate

        ,FinalDate

    )

)

Return Final

[SamWiseOwl · Accepted solution]

For your second question there are two ways to do it.

The first only averages by how many months there is data for:

Rolling Year average =

Var InitialDate =

DateAdd(FIRSTDATE('month avg'[Month Year]), -11, MONTH)

Var FinalDate =

LASTDATE('month avg'[Month Year])

var Final =

CALCULATE(

    AVERAGE('month avg'[Total Sales Per Month])

    ,ALLSELECTED('month avg'[Month Year])

    ,DATESBETWEEN(

        'month avg'[Month Year]

        ,InitialDate

        ,FinalDate

    )

)

Return Final

The second always averages by 12 regardless of how many months there are:

Forced Rolling Year average =

Var InitialDate =

DateAdd(FIRSTDATE('month avg'[Month Year]), -11, MONTH)

Var FinalDate =

LASTDATE('month avg'[Month Year])

var Final =

CALCULATE(

    SUM('month avg'[Total Sales Per Month])

    ,ALLSELECTED('month avg'[Month Year])

    ,DATESBETWEEN(

        'month avg'[Month Year]

        ,InitialDate

        ,FinalDate

    )

)/ 12

Return Final

Basically do you want to always divide by 12 or by the number of months you have.