Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Calculate an average from a Sum by Month

Hi Guys, 


I want the average from a time Period.

In one Table i have 2 Rows like this:  


Date:                  Values:                

01.01.2016        100

01.01.2016        50

01.01.2016        10

01.02.2016        50

01.02.2016        50

01.02.2016        20



If i creat visuals with average i only get the full average of all data ( 46,6666) 

but i need it per Month  '
average  01/2016 = 53,33

              02/2016 = 40

I tryed a lot of things with the little DAX i know but nothing worked out correctly. 


thank you in advance



Hi Mertsch,


I have tested it on my local environment, you can add calculated column to display the month name using the DAX below
Month = MONTH(MonthAverage[Date])

And then create a measure
MeasureAverage = SUM(MonthAverage[Total])/DISTINCTCOUNT(MonthAverage[Month])



Charlie Liao

View solution in original post

Advocate II
Advocate II

Hi All, (funny I came here for an average per month of a sum - which is elluding me)...


However I deal with dates and odd data a lot, supprised you guys are having an issue...

So a percentage is  numerator/denominator (then click on the % in your column tools) easy enough... but over time can be an issue. So lets say you create a simple % measure, but wan tot look at it over time. (say Month to date, last month and year to date).

1) in your  hospital data duplicate your date column and make sure its set as "date" in formatting

2) if you dont have a date table click on modelling 'New Table' and paste this in:

Date =
VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
VAR currentDay = [Date]
VAR days = DAY( currentDay )
VAR months = MONTH ( currentDay )
VAR years = YEAR ( currentDay )
VAR nowYear = YEAR( TODAY() )
VAR nowMonth = MONTH( TODAY() )
VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
VAR todayNum = WEEKDAY( TODAY() )
VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
"day", days,
"month", months,
"year", years,
"day index", dayIndex,
"week index", weekIndex,
"month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
"year index", INT( years - nowYear )
3) Link your new date table to your hospital data's duplicate 'date' column in your data (easy this way trust me)
4) Now create a new % measure (say "HAC_CSV_Data[% of HAC's]" and using this create 3 new date range measures:
% of HAC's (MTD) = CALCULATE(HAC_CSV_Data[% of HAC's], DATESMTD('Date'[Date]))
% of HAC's (Prev MTD) = CALCULATE(HAC_CSV_Data[% of HAC's], PREVIOUSMONTH(DATESMTD('Date'[Date])))
% of HAC's (YTD) = TOTALYTD(CALCULATE(HAC_CSV_Data[% of HAC's]),'Date'[Date])
5) once those are created you will need to click on the column tools for each one and format them as % 
6) Using the Date table you created and linked to your data - add the Year and Month (only) as a hierarchy into a table visualization, and then add your new 3 measures, just to check they work.
For graphs/charts that flow over years I use the MTD measure the most, not the YTD, as it just works best. 
The 3 measures work great in cards btw - flawlessly! (for me) and to do date selections for the end user I add a "Chiclet" into PBi from the free apps and have one for year, and one for month. (or create a new date column = format as dd/mm/yyyy - and to sort, which is a nightmare, create another column formatted as mm/yyyy - and sort using that.) Chiclet works best for me due to the sheer amount of filtering I use in measures, but only if I use the MTD, Prev MTD and YTD cards, otherwise a normal date slicer is fine...
PS - I work with Hospital data all day every day - so this should be easy stuff (wait till you get to title changes and chart x axis changes by selection 🙂
Frequent Visitor

I have the quantity of employee per month, need to calculate the average per month

Final Qty                  Average per month

January :100            100

February: 120           110

March:   90               105

April ::  100               95


Average= (Qty 1+Qty)/2

i am struggling with this , if someone can help me, 🙂 😞 

Frequent Visitor

edit: sorry i explane it wrong.... 


what i need is:  


the final average:

01/2016 = Sum 160 

02/2016 = Sum 120


average: 140 



Try creating a calculated column like:


Month = MONTH([Date])

Then add your Average measure and Month to a table.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Looks like you want to get the average by Summing the amount and dividing it by the number of months/distinct dates rather than dividing by the number of rows. 

This might help, write following DAX measures:







Check out the Chicagoland Power BI User Group

Not applicable



I have one more question here, i ahave one Power Bi template, which has Hours by month Operating Group wise.


Cureently in Power Bi i am using Matrx visual for this and i have the data from February 2018 to December 2018, i .e for 10 months.


But when i used the formual 


Average Hours = SUM('MC Payroll and Hours'[Hours]) / DISTINCTCOUNT('Dates'[Month].[Month])
DISTINCTCOUNT('Dates'[Month].[Month])    it was taking months count as 12 not 10, is there any way that i can write code for taking only 10 months.(of visual has 10 months data, months count should come to 10, if visual has data for 8 months, count should come to 8.)
My Financial Year is September to Aug.

Simple & Effective solution.  Cheers

Frequent Visitor

well it wokrs for the full tabel but if i used filters it dont work for it. 


( 140 is correct for all data ) 




But after using Filters it will be still 140.  it need to be 60 in that case 





There must be a other way to have it more flexible with using filters

The data need to be calculated acording to the used filters and visual in front end.


May it works with GROUPBY Month Dates?! But i dont get the DAX work

= GROUPBY (Tabelle1;Tabelle1[Month];“TEST2”;SUM(CURRENTGROUP();Tabelle1[Total])) 


Hi Mertsch,


I have tested it on my local environment, you can add calculated column to display the month name using the DAX below
Month = MONTH(MonthAverage[Date])

And then create a measure
MeasureAverage = SUM(MonthAverage[Total])/DISTINCTCOUNT(MonthAverage[Month])



Charlie Liao

Thanks, this helps.

But, I have a slightly different scenario. 

I am looking at tickets closed by users in a date range.
In report, say, I selected the Closed Date Range as 1/Aug - 31/Jan (6 Months)

Now, I am facing the following mismatch.

UserTickets ClosedDistinct Closed Months

Calculated Average

Actual Average






These users worked all six months - but they closed no tickets in some months. Still the average is showing better than actual.

How do I rectify this?

This does not work if the data covers a period longer than a year as multiples will appear over multiple years, is there another way?

substitute the followign for the month formula:  =format([date], "YYYYMM")


I am a beginner in Power BI. I want to arrive at % of surgeries performed by each doctor for the month. i. e. total surgeries by a doctor in a month DIVIDED BY total surgeries in the month. 




When I used the solution in a measure as "MonthlySurgeries = sum(tblReportPaed[Surgeries])/DISTINCTCOUNT(tblReportPaed[MonthNumber])" it generates an error at the bottom of the screen which reads as "TABLE: tblReportPaed (92 Rows) COLUMN: Measure (0 distinct values)".

Where am I going wrong?


Appreciate in advance.



I have similar report but it has 3 years data.

I'm looking for average calculation as below

=total year production value/no. of months

kindly provide solution


What if we want to use this measure with a date drill down, how would it work?



Thats what im looking for ! 

Thanks a lot ! 


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors