DAX Help - Average Excluding zero and SAME PERIOD LASTYEAR

[Jitmondo]

Hi,

stuggling to get the below formula to work and as it is shows blanks...

CALCULATE(AVERAGE(FIELD]),

FILTER(ALL(Dates),Dates[Year]=max(Dates[Year])-1), SAMEPERIODLASTYEAR(Dates[Date]),

FILTER(TABLE, FIELD] <> 0))

right now the visual this is for has a sameperiod last year average which works however includes zeros which I dont want.

I am trying to add the exclude zero part of the formula but keep getting blank?

any ideas on what I could do here?

thanks in advance

[bcdobbs · Accepted solution]

Would be easier to diagnose with a demo pbix file (remove sensitive data).

I think however you're hitting an issue because in FILTER(TABLE, [FIELD] <> 0) you're putting the whole table into the filter context, which has the originally selected dates and then another filter with last years dates.

Try:

CALCULATE(
	AVERAGE( TableName[FIELD] ),
	SAMEPERIODLASTYEAR( Dates[Date] ),
	TableName[FIELD] <> 0
)

If you specifically need the:
FILTER(ALL(Dates),Dates[Year]=max(Dates[Year])-1), SAMEPERIODLASTYEAR(Dates[Date]) that can go back in safely however I think the above should work in most situations where a year exists in the filter context already.

Other thoughts:

• Make sure your date table has full years.
• Make sure your date table is marked as a date table.
• Does your fact table have data for the previous period in question?

[Jitmondo]

bcdobbs you are a rockstar ! thanks this worked exactly as you said.