Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

KPI visual target help

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 ov...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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