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 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!

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

     

3 Replies

    • 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