Forum Discussion

Jitmondo's avatar
Jitmondo
Icon for Helper III rankHelper III
4 years ago
Solved

DAX Help - Average Excluding zero and SAME PERIOD LASTYEAR

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
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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?

2 Replies

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Jitmondo
      Icon for Helper III rankHelper III
      4 years ago

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