Skip to main content
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.

Helper II
Helper II

Average excluding Zeros and Blanks

Hi everyone,


I need some help writing a DAX measure to CALCULATE the average excluding ZEROS AND blanks ?


My data is arranged in the following manner:


Brand      Period            Value

X               2018M01       2

X               2018M01       0

Y               2018M02       3

Z               2018M02       0


The AVERAGE should be calculated by BRAND and for a period range of the LAST 12 Months i.e 2018M01.......up until 2018M12.

The formulas should not count any PERIODS that have Blank values or Zeros.

The last 12 Months should be dynamic. using my Offsets Column.

The OFFSETS in my calendar for EXAMPLE the last month is -1 and previous  month-2 etc.


So I am assuming somewheer in the AVERAGE formula, we need to include this offsets for last 12 months so >=-12 and <=-1 ?



Responsive Resident
Responsive Resident

I have a better / favorite way here if you are dealing with measures -- in case this helps!



  VAR _Numerator = [//Measure1]+[//Measure2]+[//Measure3] 
  VAR _Denom = 
        VAR _1 = if (ISBLANK([//Measure1]), 0, 1)
        VAR _2 = if (ISBLANK([//Measure2]), 0, 1)
        VAR _3 =  if (ISBLANK([//Measure3]), 0, 1)
    _Numerator / _Denom)



Community Support
Community Support

hi, @blytonpereira

After my research, you may try this way:

1. for excluding Zeros and Blanks, you could add a conditional like this

Measure = CALCULATE(AVERAGE(Table1[Sales]),Table1[Sales]<>0||Table1[Sales]<>BLANK())

2. for rolling 12 months, you'd better use a date column instead of Period (eg. 2018M01) with Time-intelligence functions.

For example, you could convert  Period to date (eg. 2018M01 ->1/1/2018), then create a relationship with the calendar table. then calculate rolling 12 months average. of course, you could add period/ year/ month column in the calendar table too.

here a blog for you refer to:


By the way, you could use built-in New quick measure and select rolling average to quick and easily create this measure.

When use this way create the measure, you need to add the conditional to exclude Zeros and Blanks manually.



Sales rolling average = 
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = ENDOFMONTH('Table1'[Date].[Date])
			CALCULATE(SUM('Table1'[Sales]), ALL('Table1'[Date].[Day]),Table1[Sales]<>0||Table1[Sales]<>BLANK())


Best Regards,


Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


In fact the

Table1[Value] <> BLANK ()

is probably not necessary as AVERAGE( ) ignores blanks already

Impactful Individual
Impactful Individual

Can you share a sample of the expected result? 

A sample of the expected result I am looking for is as below:


Brand     Average Last 12 Months


X                 the average

Y               the average

Z                  the average


What is the question exactly? How to deal with the blanks or all the rest?

The questions is how to write the DAX measure to calcualte the average of the last 12 Periods, ignoring any zeros AND blanks ??


The last 12 months should be dynamic and hence in the measure the offsets column that I have should be included.


Here's one option . The code below requires a 'Date' table, that you probably have already.  

Haven't tested it. If you provided a sample of your data I could but in any case you get the idea.


Measure =
    AVERAGE ( Table1[Value] );
    Table1[Value] <> BLANK ();
    Table1[Value] <> 0;
        LASTDATE ( 'Date'[Date] )



I treid this method but it does not seem to work in my dataset. Please see the attached EXCEL example file for the data.


I am linking the Period column in my Data tab with the Period column in my Calendar tab. (unique values) = Many to One relationship


Then in the average DAX measure I would like to apply some filters:

Profile = "STAT" or Profile = "MI"

Offset >=1 or <= 12 to select the next 12 months







Nope, it cannot work as the set is organised. You'd need to create a proper (standard) Calendar table with a YearMoth field if you want to keep using the period names you have. The Offset field should be part of the your 'Data' table, not part of 'Calendar'     

Helpful resources

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


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.