cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iamprajot Established Member
Established Member

Average of 3 Months

Hi

I need Average of the SUM of the Total of a Month and previous 2 Months so it will be 3 Months Average in front of each Month.

As there are multiple transactions in a month so the logic I am looking for is to SUM(Total) for each Month and then Evaluate Average for Current Month and Previous 2.

 

for e.g.

Jan shows Average of Jan

Feb shows Average of Jan-Feb

Mar shows Average of Jan-Feb-Mar

Apr shows Average of Feb-Mar-Apr

 

Data is here,

 

OrderDateYearMonthRegionRepItemUnitsUnit CostTotal
1/6/162016JanEastJonesPencil  0
1/23/162016JanCentralKivellBinder5019.99999.5
2/9/162016FebCentralJardinePencil364.99179.64
2/26/162016FebCentralGillPen2719.99539.73
3/15/162016MarWestSorvinoPencil562.99167.44
4/1/162016AprEastJonesBinder604.99299.4
4/18/162016AprCentralAndrewsPencil751.99149.25
5/5/162016MayCentralJardinePencil904.99449.1
5/22/162016MayWestThompsonPencil321.9963.68
6/8/162016JunEastJonesBinder608.99539.4
6/25/162016JunCentralMorganPencil904.99449.1
7/12/162016JulEastHowardBinder291.9957.71
7/29/162016JulEastParentBinder8119.991619.19
8/15/162016AugEastJonesPencil354.99174.65
9/1/162016SepCentralSmithDesk  0
9/18/162016SepEastJonesPen Set1615.99255.84
10/5/162016OctCentralMorganBinder288.99251.72
10/22/162016OctEastJonesPen648.99575.36
11/8/162016NovEastParentPen1519.99299.85
11/25/162016NovCentralKivellPen Set  0
12/12/162016DecCentralSmithPencil671.2986.43
12/29/162016DecEastParentPen Set7415.991183.26
1/15/172017JanCentralGillBinder468.99413.54
2/1/172017FebCentralSmithBinder87151305
2/18/172017FebEastJonesBinder  0
3/7/172017MarWestSorvinoBinder  0
3/24/172017MarCentralJardinePen Set504.99249.5
4/10/172017AprCentralAndrewsPencil661.99131.34
4/27/172017AprEastHowardPen  0
5/14/172017MayCentralGillPencil531.2968.37
5/31/172017MayCentralGillBinder808.99719.2
6/17/172017JunCentralKivellDesk  0
7/4/172017JulEastJonesPen Set624.99309.38
7/21/172017JulCentralMorganPen Set5512.49686.95
8/7/172017AugCentralKivellPen Set4223.951005.9
8/24/172017AugWestSorvinoDesk  0
9/10/172017SepCentralGillPencil  0
9/27/172017SepWestSorvinoPen761.99151.24
10/14/172017OctWestThompsonBinder5719.991139.43
10/31/172017OctCentralAndrewsPencil141.2918.06
11/17/172017NovCentralJardineBinder114.9954.89
12/4/172017DecCentralJardineBinder  0
12/21/172017DecCentralAndrewsBinder284.99139.72
 
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Average of 3 Months

Hi @iamprajot

 

You should set up your data model with a Date table containing a YearMonth column of some sort, related to your "Sales" table (I'll assume that's the name).

Something like this:image.pngSample data model

Then you can define measures like this:

Sales = 
SUM ( Sales[Total] )

 

Sales 3 Month Rolling Average = 
CALCULATE (
    AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [Sales] ),
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
)

 

The AVERAGEX function will automatically exclude months that either don't exist or have BLANK sales, so that in Jan-16 only one month will be averaged for example.

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

6 REPLIES 6
OwenAuger Super Contributor
Super Contributor

Re: Average of 3 Months

Hi @iamprajot

 

You should set up your data model with a Date table containing a YearMonth column of some sort, related to your "Sales" table (I'll assume that's the name).

Something like this:image.pngSample data model

Then you can define measures like this:

Sales = 
SUM ( Sales[Total] )

 

Sales 3 Month Rolling Average = 
CALCULATE (
    AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [Sales] ),
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
)

 

The AVERAGEX function will automatically exclude months that either don't exist or have BLANK sales, so that in Jan-16 only one month will be averaged for example.

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

iamprajot Established Member
Established Member

Re: Average of 3 Months

Thanks but I have already tried this and few other ways but my problem is different, no matter if you "create a separate Date Table and create relation" or "Create a Date Calculated Column", this will not solve the problem.
Let me explain the difference what I found, I can get Sum of Total for every Month in Pivot Table and then Add 3 Months and divide it with 3
but in Power BI, I can get Sum of Total for every Month BUT when it comes to Average, Power BI does not add 3 months, instead it adds 2-3 transactions of a month and divide it with number of transactions OR add Jan-Feb-Mar but DO NOT DIVIDE IT WITH 3 instead divided it with number of transactions in Jan-Feb-Mar, which is 6.

 

What I am thinking is to use SUMMARIZE function to create a Table with Dates and Sum of Total and use that table inside ADDCOLUMNS in which I could use the DATESINPERIOD.

OwenAuger Super Contributor
Super Contributor

Re: Average of 3 Months

Hi again @iamprajot

 

The expression I used above within the measure AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [Sales] ) should do what you want.

Using AVERAGEX this way, [Sales] is calculated for each YearMonth value (in the context of the measure it is at most 3 values), and these are then averaged with equal weighting.

 

Here is a sample pbix that illustrates the calculation.

PBIX link

 

An excerpt from a table in that file looks like this.

  • Jan-16 rolling avg = (1,000)/1 = 1,000
  • Feb-16 rolling avg = (1,000 + 719)/2 = 859
  • Mar-16 rolling avg = (1,000 + 719 + 167)/3 = 629

 

image.png

 

 

I think that's what you want. Can you get something similar working in your model?

 

Regards,
Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




iamprajot Established Member
Established Member

Re: Average of 3 Months

Sorry and yeah it's working but in order for it to work a separate date table is mandatory.

I was neglecting the Calendar Table and tried creating those date columns within the Data Table so that's why it was not working.

 

Also I got another help and found the below which is the same thing except there is no need to create the Calendar Table.

MAT =
CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            Data,
            Data[OrderDate].[Year],
            Data[OrderDate].[Month],
            "MAT", SUM ( Data[Total] )
        ),
        [MAT]
    ),
    DATESINPERIOD (
        Data[OrderDate].[Date],
        LASTDATE ( Data[OrderDate].[Date] ),
        -3,
        MONTH
    )
)

 

Thanks so much for help, now I am clear about both concepts.

HiltonM Frequent Visitor
Frequent Visitor

Re: Average of 3 Months

Hi, i have tried the formula used above and also tried this formula pasted, but i keep getting the cumulative of what im calculating and not the average,is there something wrong with my dax?

 

3 month avg = CALCULATE(AVERAGEX(Dim_Brokerage;Dim_Brokerage[Brokerage]);DATESINPERIOD(Dim_Date[Date];LASTDATE(Dim_Date[Date]);-3;MONTH))

VivianX Frequent Visitor
Frequent Visitor

Re: Average of 3 Months

Great solution. Thank you for sharing @OwenAuger

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 138 members 1,523 guests
Please welcome our newest community members: