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 ?
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 = CALCULATE ( AVERAGE ( Table1[Value] ); Table1[Value] <> BLANK (); Table1[Value] <> 0; DATESBETWEEN ( 'Date'[Date]; NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ); 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'
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 = IF( ISFILTERED('Table1'[Date]), 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]) VAR __DATE_PERIOD = DATESBETWEEN( 'Table1'[Date].[Date], STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)), __LAST_DATE ) RETURN AVERAGEX( CALCULATETABLE( SUMMARIZE( VALUES('Table1'), 'Table1'[Date].[Year], 'Table1'[Date].[QuarterNo], 'Table1'[Date].[Quarter], 'Table1'[Date].[MonthNo], 'Table1'[Date].[Month] ), __DATE_PERIOD ), CALCULATE(SUM('Table1'[Sales]), ALL('Table1'[Date].[Day]),Table1[Sales]<>0||Table1[Sales]<>BLANK()) ) )