cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Responsive Resident

## Rolling average sales calculation for financial year

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!

1 ACCEPTED SOLUTION
Community Support

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=
_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'),

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

4 REPLIES 4
Community Support

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=
_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'),

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

Resolver II

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

Resolver II

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

Responsive Resident

hi @jolind1996 ,

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors