Rolling average sales calculation for financial year

[vs_7]

Hi guys,

I am working on an analysis to calculate average sales in every fiscal year.

Sample data

Branch head	Apr	May	June	July	August	September	October	November	December	January	February	March	Total	Expedcted AVERAGE
Alok K	10.33	8.39	8.40	9.80	9.330	13.598	11.596	18.160	13.87	9.64	11.43	10.79	135.34	11.28
Amit K	3.06	2.48	2.30	3.08	2.526	3.127	4.416	4.043	3.29	4.16	4.32	3.12	39.92	3.33
Amit S	4.13	2.86	3.24	2.45	2.972	2.451	2.306	2.364	2.56	2.29	2.57	1.92	32.11	2.68
Anil P	8.44	8.99	9.30	6.86	4.675	7.110	12.659	13.013	8.57	5.49	5.93	3.47	94.50	7.87
Arun R	4.24	2.37	4.91	5.23	7.184	11.471	7.450	6.462	4.33	3.92	8.01	4.63	70.18	5.85
Arvind P	2.87	4.44	3.30	3.70	6.440	4.698	5.751	5.546	6.34	5.85	7.91	5.70	62.56	5.21
Chirag A	12.27	14.40	28.25	21.35	19.473	27.957	75.248	23.070	23.41	11.72	13.68	10.01	280.83	23.40

For my company, every fiscal year starts from April each year.

Thus, if i have a slicer to select year month Aor-22, the average sales for

Branch head Alok k should be: (10.33+8.39+8.40+9.80+9.33+13.59+11.59+18.16+13.87+9.6+11.4+10.79)/12

Sales is a CALCULATED MEASURE which is Sales = sum(customer sales).

I have tried to get the average by the measure below:

Rolling Avg 12M =

VAR NumOfMonths =  12

VAR Rollingsum = CALCULATE(SUM(Group_by_receivables[TotalSales]),DATESINPERIOD(Group_by_receivables[ASONDATE],

                LASTDATE(Group_by_receivables[ASONDATE]),-NumOfMonths,YEAR)

)

return

Rollingsum/NumOfMonths

Did not return the correct number as per what I expected above.

May I know if anyone can help showing a formula that works this way?

Thank you!

@amitchandak

[v-yangliu-msft · Accepted solution]

Hi  @vs_7 ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Slicer =
 DISTINCT('Group_by_receivables'[Year_Month])

Table =
var _table=
SUMMARIZE('Group_by_receivables','Group_by_receivables'[Month],
"mindate",MINX(FILTER(ALL('Group_by_receivables'),'Group_by_receivables'[Month]=EARLIER('Group_by_receivables'[Month])),[ASONDATE]))
var _table2=
{"AVG"}
var _table3=
ADDCOLUMNS(
    _table2,"mindate",MAX('Group_by_receivables'[ASONDATE])+1)
return
UNION(
    _table,_table3)

2. Create measure.

Measure =
var _select=SELECTEDVALUE('Slicer'[Year_Month])
var _mindate=MINX(FILTER(ALL('Group_by_receivables'),'Group_by_receivables'[Year_Month]=_select),[ASONDATE])
var _date1=EOMONTH(_mindate,-1)
var _date2=EOMONTH(_mindate,-13)
return
IF(
    MAX('Table'[Month])="AVG",
SUMX(
  FILTER(ALL('Group_by_receivables'),'Group_by_receivables'[Branch head]=MAX('Group_by_receivables'[Branch head])&&'Group_by_receivables'[ASONDATE]>=_date2&&'Group_by_receivables'[ASONDATE]<=_date1),[TotalSales])/12,   SUMX(
    FILTER(ALL('Group_by_receivables'),
    'Group_by_receivables'[Branch head]=MAX('Group_by_receivables'[Branch head])&&'Group_by_receivables'[Month]=MAX('Table'[Month])&&'Group_by_receivables'[ASONDATE]>=_date2&&'Group_by_receivables'[ASONDATE]<=_date1),[TotalSales]))

3. Result:

Best Regards,

Liu Yang

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

[jolind1996]

Hi vs_7,

If you want to calculate the average sales for each financial year and filter the year from a calendar table, you can modify the measure to use the selected year from the calendar table. Here is an example of how you can do this:

Average Sales =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR StartDate = DATE(SelectedYear, 4, 1)
VAR EndDate = DATE(SelectedYear + 1, 3, 31)
VAR AverageSales = AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Date] >= StartDate && 'Table'[Date] <= EndDate
    ),
    [Sales]
)
RETURN AverageSales

In this measure, 'Calendar' is the name of the calendar table, 'Calendar'[Year] is the column containing the years, 'Table' is the name of the table containing the sales data, 'Table'[Date] is the column containing the dates, and [Sales] is the measure that calculates the sales. The measure uses the SELECTEDVALUE function to get the selected year from the calendar table, and the FILTER and AVERAGEX functions to calculate the average sales for the selected financial year.

Please note that you will need to replace 'Calendar', 'Calendar'[Year], 'Table', 'Table'[Date], and [Sales] with the actual names of your calendar table, year column, sales table, date column, and sales measure. Also, make sure that the calendar table is correctly set up and that the slicer is correctly set up to filter the 'Calendar'[Year] column.

Best regards,

Johannes

[jolind1996]

It looks like you are trying to calculate the average sales for each branch head for a given fiscal year using a calculated measure in Power BI. You mentioned that the measure you tried did not return the expected result.

One way to calculate the average sales for each branch head for a given fiscal year is to use the TOTALYTD function in combination with the AVERAGE function. Here is an example of a measure that calculates the average sales for each branch head for the fiscal year starting in April:

Average Sales = 
    AVERAGE(
        TOTALYTD(
            SUM('Table'[Sales]),
            'Table'[Date],
            "03/31"
        )
    )

In this measure, 'Table'[Sales] is the column containing the sales data, 'Table'[Date] is the column containing the dates, and "03/31" specifies that the fiscal year ends on March 31st.

I hope this helps! Let me know if you have any further questions.

Best regards,

Johannes

[vs_7]

hi @jolind1996 ,

Thanks for your response.

As i'm calculating average for financial year wise and i have to filter year from calender table accordingly the measure should work.