calculating last year average to add in a pivot table

[Anonymous]

Hi All, 

I would appreciate your help on a measure that I have spent several days trying to add to a pivot table. 

I would like to calculate last year (2018) average nb of leads, using the data format below. I have tried to use several DAX formulas but nothing worked.

My aim is to recreate the following pivot table: 

Many thanks in advance. 

Veronica

[v-yuta-msft]

@Anonymous ,

You may create a measure using DAX like pattern below:

Result =
CALCULATE (
    AVERAGE ( Table[Leads(nb) by Channel] ),
    FILTER ( Table, YEAR ( Table[Date] ) = YEAR ( TODAY () ) - 1 )
)

Community Support Team _ Jimmy Tao

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

[Anonymous · Accepted solution]

Thank you for taking the time to get back to me. Unfortuantely, the formula returns an incorrect average.

I have checked and no filters were applied. The above formula seems to return a monthly average for 2018.

I found that the following DAX formual works:

2018 AVG Leads= CALCULATE (

                                  SUM ( Table[Leads(nb) by Channel]),

                                  FILTER(Table, AND (Table[Date],(Table[Year]=2018))))/12