Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
vs_7
Responsive Resident
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 headAprMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarchTotalExpedcted AVERAGE
Alok K10.338.398.409.809.33013.59811.59618.16013.879.6411.4310.79135.34    11.28
Amit K3.062.482.303.082.5263.1274.4164.0433.294.164.323.1239.92    3.33
Amit S4.132.863.242.452.9722.4512.3062.3642.562.292.571.9232.11    2.68
Anil P8.448.999.306.864.6757.11012.65913.0138.575.495.933.4794.50    7.87
Arun R4.242.374.915.237.18411.4717.4506.4624.333.928.014.6370.18    5.85
Arvind P2.874.443.303.706.4404.6985.7515.5466.345.857.915.7062.56    5.21
Chirag A12.2714.4028.2521.3519.47327.95775.24823.07023.4111.7213.6810.01280.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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @vs_7 ,

I created some data:

vyangliumsft_0-1708932582356.png

 

Here are the steps you can follow:

1. Create calculated table.

Slicer =
 DISTINCT('Group_by_receivables'[Year_Month])

vyangliumsft_1-1708932582359.png

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)

vyangliumsft_2-1708932607256.png

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:

vyangliumsft_3-1708932635609.png

 

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @vs_7 ,

I created some data:

vyangliumsft_0-1708932582356.png

 

Here are the steps you can follow:

1. Create calculated table.

Slicer =
 DISTINCT('Group_by_receivables'[Year_Month])

vyangliumsft_1-1708932582359.png

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)

vyangliumsft_2-1708932607256.png

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:

vyangliumsft_3-1708932635609.png

 

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
Resolver II
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

jolind1996
Resolver II
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

vs_7
Responsive Resident
Responsive Resident

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.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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