Percent of Goal

[jracer007]

Hello community,
I need your great help to create a card that must contain the following values:

It must show the percentage that I have with respect to the goal that is in the second image:

I clarify that the value of the second column corresponds to the current month (first column).

Thanks.

[v-yuezhe-msft]

@jracer007,

1. In Power BI Desktop, create date table following the guide in this blog.

2. Create relationship between the first table and date table using date field, create relationship between the second table and date table using date field.

3. Create measure in the first table.
sum1 = SUM('Table 1'[Value])

4. Create measure in the second table.
sum2 = SUM('Table 2'[MITIGOAPP v1])

5. Create measure in the first table.
percentage = [sum1]/[sum2]

6. Create a  table visual using YearMonth field of date table and the percentage measure.

If the above steps don’t help, please elaborate your logic and post expected result in table format.

Regards,
Lydia

[jracer007]

The current value (column K) is equivalent to the current day and the goal (MITIGOAPP v1) is equivalent to the current month.
here tines the two files:
https://docs.google.com/spreadsheets/d/1jvF4xmXOE_TdBfGvwsJNWVqFMpUcoht5wGm_9Gf9hJI/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1OnHTSvbXXmg666DO-ey5tA2GqmeJ8ICiRej03E69CwU/edit?usp=sharing

[v-yuezhe-msft]

@jracer007，

What is your expected result ? You can take Dec 2017 for example. And I see some text values in the second column in your first file, do you share correct data in the first file？

Regards,
Lydia

[jracer007]

I'll give you an example,
The target value must be equal to the one in the current month (calendar). then it should be 201.515, and the current value should be from the last day. Example if we are on 12/12/2017 the value should be 113.240

Finally, the missing percentage must be displayed to reach that objective value.

[v-yuezhe-msft · Accepted solution]

@jracer007，

The first two steps are same as my first reply, change step3 and step4, ignore step5 and step6.

3. Create the following columns in the first table

Year = YEAR(Table1[Date])

Month= MONTH(Table1[Date])

max date of nonblank = CALCULATE(MAX(Table1[Date]),FILTER(Table1,Table1[Year]=EARLIER(Table1[Year])&& Table1[Month]=EARLIER(Table1[Month])),NOT(ISBLANK(Table1[Active user])))

last value per month = CALCULATE(FIRSTNONBLANK(Table1[Active user],Table1[Active user]),FILTER(Table1,Table1[Date]=Table1[max date of nonblank] && Table1[Year]=EARLIER(Table1[Year])&& Table1[Month]=EARLIER(Table1[Month])))

percentage = DIVIDE(Table1[last value per month],RELATED(Table2[MITIGOAPP v1]))

4. Create a table visual as below.

Regards,
Lydia

[jracer007]

Great!!