Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iamprajot
Responsive Resident
Responsive Resident

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
OwenAuger
Super User
Super User

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:Sample data modelSample 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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
OwenAuger
Super User
Super User

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:Sample data modelSample 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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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))

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.

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Great solution. Thank you for sharing @OwenAuger

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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