Year targets vs. Date table

[Anonymous]

Source: Tabular model 🙂

I have 'Year Target' table containing columns: Department - Date - TargetArea - Min - Mid - Max.
I have made measure for min, mid and max by TargetArea which I can use on report.
(e.g. Material Y Target max:=CALCULATE(AVERAGE('YearTarget'[Max]);'YearTarget'[TargetArea]="Material";'Year Target'[Max]<>0)

Date in this table is for 1st day of Jan in selected year (e.g. 2020-01-01, 2019-01-01 etc.). 
If I now link that date field to DimDate I will get target measures only for that month.

How to update measures so that I have correct target for 2020 even I have any of the months selected ?
And if I select any month from 2019 it shows targets for that year?

[Anonymous · Accepted solution]

[Good Measure] =
CALCULATE(
    AVERAGE( YearTarget[Max] ),
    // You want to wrap conditions in KEEPFILTERS
    // (almost) every time you know you're going to
    // slice and dice by the attribute involved.
    KEEPFILTERS( YearTarget[TargetArea] = "Material" ),
    YearTarget[Max] <> 0, // I would be careful with this condition...
    ALL( DimDate ),
    VALUES( DimDate[Year] )
)

Best

D

[kentyler]

When you put this measure on a graphic (a table say) Material Y Target max:=CALCULATE(AVERAGE('YearTarget'[Max]);'YearTarget'[TargetArea]="Material";'Year Target'[Max]<>0)

then the filter context it runs it is limited to the row in the table....which is why is shows only that month

CALCULATE can change the external filter context by add an ALL argument

CALCULATE(AVERAGE('YearTarget'[Max]);'YearTarget'[TargetArea]="Material";'Year Target'[Max]<>0;ALL('YearTarget'))
or maybe ALL('YearTarget'[Max])
this will remove the filter on the YearTarget table and you should get the full average.