cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Set a KPI Goal based on previous dates

I have several KPI Visuals in my report and the value is shown correctly, however I can not set the Goal right. My report is generated according to the dates selected, so if I have selected 2016-07-10 through 2016-07-15 I want the goal to be the same metric from the prevoius 5 days (2016-07-05 - 2016-07-10). For example if the metric value is 65% for 2016-07-10 - 2016-07-15 and 70% for 2016-07-05 - 2016-07-10 the goal would say 5%. I have created two measures for the dates and they are correct. I then created another measure to be the goal for the metric and the code is:

Metric_Goal= CALCULATE([Metric], FILTER(Table, Table[Date]>=[StartDate]), FILTER(Table, Table[Date]<=[EndDate]))

Where StartDate=2016-07-05 and EndDate=2016-07-10, but it's not working because it's giving the same value (65% as the metric value and 65% as the goal).

Can someone help me fix this?

1 ACCEPTED SOLUTION
Microsoft

@Anonymous

My report is generated according to the dates selected, so if I have selected 2016-07-10 through 2016-07-15 I want the goal to be the same metric from the prevoius 5 days (2016-07-05 - 2016-07-10).

In this scenario, you should be able to use DATEADD and DATEDIFF functions to calculate the previous days' Metric according to the date selections. The formula below is for your reference.

```Metric_Goal =
CALCULATE (
[Metric],
DATEADD ( Table[Date], - DATEDIFF ( [StartDate], [EndDate], DAY ), DAY )
)```

Regards

2 REPLIES 2
Microsoft

@Anonymous

My report is generated according to the dates selected, so if I have selected 2016-07-10 through 2016-07-15 I want the goal to be the same metric from the prevoius 5 days (2016-07-05 - 2016-07-10).

In this scenario, you should be able to use DATEADD and DATEDIFF functions to calculate the previous days' Metric according to the date selections. The formula below is for your reference.

```Metric_Goal =
CALCULATE (
[Metric],
DATEADD ( Table[Date], - DATEDIFF ( [StartDate], [EndDate], DAY ), DAY )
)```

Regards

Anonymous
Not applicable

Thank you! However, I used DATESBETWEEN(StartDate, EndDate) because that's what I needed, but thanks for the idea it solved my problem. 🙂

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors