Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

KPI Goal as Initial Value by DateTime

I want my KPI to show the first sales number by Created Date per each project ID.  Any suggestion on how I could fix this?   For the attached sample, the expected outcome would be for the Goal val...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    It’s my pleasure to answer for you.

    According to your description,I think need to create a index column in PQ first,because your date column has duplicate data.Then create a measure.

    Like this:

     

    First Value 1 =
VAR a =
    MINX (
        FILTER (
            ALL ( data ),
            [Project ID] = SELECTEDVALUE ( data[Project ID] )
                && [Sales 1] <> BLANK ()
        ),
        [Index]
    )
RETURN
    MAXX ( FILTER ( ALL ( data ), [Index] = a ), [Sales 1] )

     

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, Anonymous 

 

It’s my pleasure to answer for you.

According to your description,I think need to create a index column in PQ first,because your date column has duplicate data.Then create a measure.

Like this:

 

First Value 1 =
VAR a =
    MINX (
        FILTER (
            ALL ( data ),
            [Project ID] = SELECTEDVALUE ( data[Project ID] )
                && [Sales 1] <> BLANK ()
        ),
        [Index]
    )
RETURN
    MAXX ( FILTER ( ALL ( data ), [Index] = a ), [Sales 1] )

 

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

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

    That actually worked! Thank you Janey. v-janeyg-msft 

     

    Any thought on fixing the trend line there?

    As you can see, the correct trend line would be the one from the second graph (color green) rather than the first graph (color red). 

    I tried to write the function as a column instead of a measure. That didn't work.

    Any thoughts?

     

     

     

    Greatly appreciated.

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi, Anonymous 

       

      The green graph was presented by the wrong goal value, and it will change after the goal value is corrected.If you feel not good, it may be that your data is not suitable for using KPIs.You need to adjust the data first.

      Here is the KPI link.You can check it.

       

      Best Regards

      Janey Guo

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.