Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic KPI measure line

Hello everybody! I have a column based diagram, showing timelyness and target measure by months . In 2020 year target was 85%. In 2021 year it is 90%. How can I change my target measure, to see 85...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi , Anonymous 

    You only need to add a measure as below:

     

    Target = 
SWITCH ( SELECTEDVALUE ( 'Table'[Date].[Year] ), 2020, 0.85, 2021, 0.9 )

     

    or

     

    Target2 = 
VAR a =
    SELECTEDVALUE ( 'Table'[Date].[Year] )
RETURN
    IF ( a = 2020, 0.85, IF ( a >= 2021, 0.9 ) )

     


    The result will show as below:

    Please check my sample file for more details.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

You can use time intelligence to have measure like examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))


Appreciate your Kudos.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.