KPI visual target help

[Anonymous]

I am using the default KPI visual, but am having trouble with a calculated target value. I am using YTD costs for the indicator and fiscal year for the trend. I would like to use the average costs over the last five years for the target, so I created a measure that calculates this. It works fine if I just put it in its own card, but it creates a strange, lower than expected amount if I put it in the target field. Any ideas?

Thanks!

[v-lili6-msft]

HI, @Anonymous 

You may refer to these two same posts I have solved:

https://community.powerbi.com/t5/Desktop/KPI-card-displays-different-value-to-card-and-table-any-way-to/m-p/607882#M289548

https://community.powerbi.com/t5/Desktop/visual-KPI-week-previous-week/m-p/644979#M309098

Please use ALL/ALLEXCEPT/ALLSELECTED function to change the row context of average measure as above. 

Best Regards,

Lin

[Anonymous]

I'm still not able to get this. I have a measure that calculate the YTD costs. If I combine this measure with the fiscal year, I get the following:

FiscalYear      Cost YTD
2014              $3,447,444
2015              $3,017,536
2016              $3,110,887
2017              $1,782,432
2018              $8,665,692
2019              $8,493,915

I'm trying to create a KPI visual that compares the 2019 Cost YTD ($8,493,915) to the average of the previous five years (2014-2018, $4,004,798). I can create a graph that correctly displays Cost YTD vs FiscalYear and I can create a table visual that outputs the above rows, but I can't figure out how to create a measure that gives the result of $4,004,798 that I can then use as the target value in the KPI visual.

Thanks again!

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could try this formula as below:

Measure 2 = 
VAR __LAST_DATE =
    ENDOFYEAR ( 'Date'[Date] )
VAR __DATE_PERIOD =
    DATESBETWEEN (
        'Date'[Date],
        STARTOFYEAR ( DATEADD ( __LAST_DATE, -6, YEAR ) ),
        ENDOFYEAR ( DATEADD ( __LAST_DATE, -1, YEAR ) )
    )
RETURN
    AVERAGEX (
        CALCULATETABLE (
            SUMMARIZE ( VALUES ( 'Date' ), 'Date'[Date].[Year] ),
            __DATE_PERIOD
        ),
        [Cost YTD]
    )

Result:

And here is pbix file, please try it.

Best Regards,

Lin