cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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 value (First) to show 5.50M instead of 4.9M since that is the first Sales 1 of Project ID 28. 

 

https://www.dropbox.com/s/viwu36d32e3suq2/saleshistorysample.pbix?dl=0

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi, @jyeh_behr 

 

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] )

 

6.png

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.

View solution in original post

5 REPLIES 5
Microsoft
Microsoft

Hi, @jyeh_behr 

 

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] )

 

6.png

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.

View solution in original post

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?

 

 

jyeh_behr_0-1604354790648.png

 

Greatly appreciated.

Hi, @jyeh_behr 

 

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.

Super User IV
Super User IV

@jyeh_behr , Create a new measure like this and try


first value =
sumx(values(Table[project id]), firstnonblankvalue(Table[Created Date], Sum(Table[Sales])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for sharing that!

I tried that measure and it still didn't show the right value for the target goal in the KPI. 

It still shows as 4.9M instead of 5.5M.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors