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% in months before 01/01/2021 and 90% - from 01/01/2021?

 

Regards, Nikolai

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.

4 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello!

    Let me explain more clearly.

    I'm telling about "target" measure. In 2020 it was 85% But from the beginning of 2021 it is 90%.

    How can I change my measure to see 85% in 2020 and 90% in 2021 on this graph?

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak , unfortunately, I didn't catch, how should I change my measure "target"?