cancel
Showing results for
Did you mean:
Helper I

## Get KPI indicator by selected time period in comparison to time period before

Hi,

iam looking for a solution for the following problem:

The dashboard user can select a time periode over a data filter visualisation.

I have the following data set:

Now I will integrate a KPI indicator for the column "reach" in comparison with the time period before.

1st example:

User selection: 24.06.2017

Total reach: 189

Time period before: 23.06.2017

Total reach: 1062

- 82,21%

2nd. example:

User selection: 23.06.2017 - 24.06.2017

Total Reach = 1.251

Time period before: 21.06.2017 - 22.06.0217

Total reach = 1.495

- 16,33%

Iam using PowerBi Desktop v2.47.4766.801. Is this possible with a new measure and dax function?

Thanks a lot

2 ACCEPTED SOLUTIONS
Super User II

Here is a little example

Basically I created two measures

reach within the selection

```reachSelection =
var minDateSelection = MINX(ALLSELECTED('reach'[date]),'reach'[date])
var maxDateSelection = MAXX(ALLSELECTED('reach'[date]),'reach'[date])
return
CALCULATE(SUM(reach[reach]),
FILTER(ALL(reach[date]),
'reach'[date] >= minDateSelection && 'reach'[date] <= maxDateSelection
)
) ```

reach < min(selection)

```reachBeforeSelection =
var minDateSelection = MINX(ALLSELECTED('reach'[date]),'reach'[date])
return
CALCULATE(SUM(reach[reach]),
FILTER(ALL(reach[date]),
'reach'[date] < minDateSelection
)
)```

Finally a division a little percent consideration

`aKPI = (Divide([reachSelection],[reachBeforeSelection])-1)*100 `

The second example looks like this

But I have to admit that I have a different result for the first exmple ...

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User II

Hey,

try this measure 🙂

```try this =
var minDateSelection = MINX('statistics','statistics'[Date])
var numberOfDatesInSelection = DISTINCTCOUNT('reach'[date])
return
calculate(
SUM(reach[reach]),
topn(numberOfDatesInSelection,
FILTER(ALL(statistics),
'statistics'[Date] <  minDateSelection
),
'statistics'[Date],
DESC
)
)```

My previous solution did not use the last days (noofdays), now i determine these days and then i'm filtering the the reach table, guess it's fixed now (hopefully)

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
17 REPLIES 17
Super User II

Here is a little example

Basically I created two measures

reach within the selection

```reachSelection =
var minDateSelection = MINX(ALLSELECTED('reach'[date]),'reach'[date])
var maxDateSelection = MAXX(ALLSELECTED('reach'[date]),'reach'[date])
return
CALCULATE(SUM(reach[reach]),
FILTER(ALL(reach[date]),
'reach'[date] >= minDateSelection && 'reach'[date] <= maxDateSelection
)
) ```

reach < min(selection)

```reachBeforeSelection =
var minDateSelection = MINX(ALLSELECTED('reach'[date]),'reach'[date])
return
CALCULATE(SUM(reach[reach]),
FILTER(ALL(reach[date]),
'reach'[date] < minDateSelection
)
)```

Finally a division a little percent consideration

`aKPI = (Divide([reachSelection],[reachBeforeSelection])-1)*100 `

The second example looks like this

But I have to admit that I have a different result for the first exmple ...

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper I

Hi Tom,

I have a question to your solution:

We have no entries in our database for sundays and holidays, so we have a date gab which will be not considered in your solution.

Table example

Date  | Hour | Reach

 22.02.2017 1 78 22.02.2017 3 839 22.02.2017 4 706 22.02.2017 5 913 22.02.2017 6 487 23.02.2017 3 430 23.02.2017 4 675 23.02.2017 5 165 25.02.2017 3 747 25.02.2017 4 704 25.02.2017 5 550 25.02.2017 6 906 26.02.2017 1 10 26.02.2017 2 657 26.02.2017 3 856 27.02.2017 9 323 27.02.2017 10 475 27.02.2017 11 691

If the user selects 26.02-27.02 as time period -> Reach: 3.002
Now the kpi comparison has to be based on time period 23.02.2017 & 25.02.2017 -> Reach: 3.271
In your solution the kpi comparison based on the 24.02.2017 & 25.02.2017 but there is no entry for the 24.02.2017-> Reach: 2.160

So i tried to edit your solution in this way:

```var minDateSelected     = MINX(ALLSELECTED('statistic'[Date]);'statistic'[Date])
var maxDateSelected     = MAXX(ALLSELECTED('statistic'[Date]);'statistic'[Date])
var dateDifference	= DATEDIFF(minDateSelected; maxDateSelected; DAY)
var workingDaysSelected = DISTINCT(statistic[Date])
var newMaxDate 		= IF(minDateSelected = maxDateSelected && WEEKDAY(minDateSelected;1)=2;  minDateSelected - 2; minDateSelected -1)
var newMinDate = ???```

With workingDaysSelecte = DISTINCT(statistic[Date] I tried to find out how many days with values are in user selection time period. If minDateSelected is a monday -> newMaxDate = minDateSelected -2 (saturday), otherwise -1

Now Iam looking for a solution to calculate newMinDate backwards: Number of workingdays before newMaxDate which has an entrie in Date column.

Is there any kind of solutions?

Best regards

Super User II

Hey,

I have to admit, that I do not fully understand what has to happen, if the upper bound and / or the lower bound of the the selection is a sunday, for this reason i added a new page to the pbix file (the link of the my first post is still valid).

I added a date table "statistics" and also two new measures using the DAX formulas FIRSTNONBLANK() and LASTNONBLANK()
These measures provide the last and first date of the reach table, maybe you can use these date to compare these dates with bounds from the selection

Please be aware that the pbix file is now somewhat "deranged" due to the fact that I'm in a little hurry. As soon as I understand what has to happen on sundays / or holidays I will fix this.

Cheers

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper I

Hi Tom,

thanks. Sorry for the misunderstanding:

The question is easy. Can I ignore blank values in the "DateSelection" and "BeforeSelection" to calculate kpi (reachBeforeSelection) indicator correctly?

Example

16.06.2017 Fr
17.06.2017 Sa

18.06.2017 So -> No Data (BLANK)

19.06.2017 Mo

20.06.2017 Tu

21.06.2017 We

22.06.2017 Th

23.06.2017 Fr

24.06.2017 Sa

25.06.2017 So -> No Data (BLANK)

26.06.2017 Mo

27.06.2017 Th

1.
User selected time period: 22.06-26.06. (NumberOfDaysWithAnalyticData = 4 Th,Fr,Sa, Mo) -> DISTINCT(reach[date])
Time period before: 4 Days with value (17.06 - 21.06)

2.
User selected time period: 19.06-19.06. (NumberOfDaysWithAnalyticData = 1 Mo) -> DISTINCT(Reach[date])
Time period before: 1 Day with value (17.06)

Weekdays can also be blank

Best regards

Super User II

Hey,

I'm sorry, but maybe I'm a little slow today. From your description I deduct that the 16th is an empty "normal" workday, no Sunday and no Holiday.

I added days in the reach table as well as in the statistics table. Please focus on Page 1 in the file

The Statististics table now starts with the 16th of June (a Friday), this day has no reach, for this reason there is just one day with data if the user selects the 19th.

Can you please describe what you expect for the KPI reachBeforeSelection if the 19th is selected and please use the values from my file.

If the problem is not visible due to my sample data, please share a file

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper I

Hi Tom,

thanks for your support.

I tried your version and you only count all values <minDateSelection for reachBeforeSelection.

I expect the following, with your demo report.

example 1:

user selection:  24.06 - 27.06
reach: 459 (3days with values | check)
reachBeforeSelection: 2557 (3days with values 21.06-23.06 | your result: 2887)

example2:
user selection: 26.06 - 27.06
reach: 270 (2 days with values | check)
reachBeforeSelection: 1251 (2 days with values 23.06 - 24.06 | your result: 3076)

example 3:
user selection: 19.06 - 19.06
reach: 110 (1 day with value | check)
reachBeforeSelection: 100 (1 day with value 17.06 | your result: 100 but only in case there is no more entry before 17.06)

I need the same time period for reachBeforeSelection like userSelection. If the user selects 4 days which includes 3 value days, I need a comparison to the last 3 days with values before.

hope you can follow 😉

thanks

Super User II

Guess now I got it: i try to rephrase your requirement

If the selection contains 3 days with values, then the KPI "before selection" is based on the last 3 dates with values before the lower bound of the selection?

If the selection contains 6 days with values, then the KPI "before selection" is based on the last 6 non blank dates before the lower bound.

Hoping now I got it what you are looking for.

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper I

Yes that it is;)

Super User II

Another question, what has to happen if there are not enough dates before the selection, in my example

selection start 2017-06-19

selection end 2017-06-28

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper I

If not enough dates before the user selection available, than take every dates before without blank.

Super User II

So understanding the question is a giant leap to the answer, but unfortunately I'm away for two to three hours, but after that "distraction" I will provide a solution 🙂

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User II

Finally there is a new pbix file

There is a 2nd measure "reachBeforeSelection sameNoOfDates" that considers the same amount of non blank days.

A lenghty name, but who cares 🙂

Hope this helps

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper I

Hi Tom,

thanks for your help, but I think there is a little bug in it.

1.

If I select the 24-27.06 (3 days with value): Reach = 459 and ReachBeforeSelection sameNoOfDates = 1062

But I expect the a value of 2.557 (23.06 & 22.06 & 21.06)

2. If I select the 22 - 23.06 (2 days with value): reach 1.894 and ReachBeforeSelectionsamNoOfDates = 663
But I expect  the value of 783 (20.06 & 21.06)

3. If select the 22 - 26.06 (4 days with value): reach: 2.213 and ReachBeforeSelectionsameNoOfdates = 663

But I expect the value of 993 (17.06 & 19.06 & 20.06 & 21.06)

I think its a little bug in it. You know how to fix it?

Thanks

Super User II

Hey,

try this measure 🙂

```try this =
var minDateSelection = MINX('statistics','statistics'[Date])
var numberOfDatesInSelection = DISTINCTCOUNT('reach'[date])
return
calculate(
SUM(reach[reach]),
topn(numberOfDatesInSelection,
FILTER(ALL(statistics),
'statistics'[Date] <  minDateSelection
),
'statistics'[Date],
DESC
)
)```

My previous solution did not use the last days (noofdays), now i determine these days and then i'm filtering the the reach table, guess it's fixed now (hopefully)

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens I wish there were double Kudos option for a post. Your solution just saved my day.

@MarcS and your question did the same for me, as I encountered exact same business requriement.

Thanks both.

Helper I

Hi Tom,

for your solution I have the following question:

For sundays or holidays we have no entries in our table, so we have some gaps.

Table example:

Date   |  Hour | Reach

22.02.2017  | 1 | 123
22.02.2017 | 3 | 435

22.02.2017 | 4 | 345

23.02.2017 | 1 | 89

23.02.2017 | 10 | 321

23.02.2017 | 11 | 124

25.02.2017 | 1 | 43

25.02.2017 | 2 | 32

26.02.2017 | 3 | 65

26.02.2017 | 4 | 78

27.02.2017 | 2 | 21

27.02.2017 | 5 | 126

If the user selects the date range - 26.02.2017 - 27.02.2017 -> Reach = 290

Now the KPI, has to compare the value with the two days before which has data: 25.02.2017 & 23.02.2017 -> Rech = 609

If I use your solution, the selected rang will be compared with the 25.02.2017 & 24.02.2017 -> Reach = 75

So I tried to edit your solution with:

```var minDateSelected = MINX(ALLSELECTED('statistic'[Date]);'statistic'[Date])
var maxDateSelected = MAXX(ALLSELECTED('statistic'[Date]);'statistic'[Date])
var dateDifference = DATEDIFF(minDateSelected; maxDateSelected; DAY)
var workingDaysSelected = DISTINCT(statistic[Date])
var offDaysSelected = dateDifference - workingDaysSelected
var newMaxDate = IF(minDateSelected = maxDateSelected && WEEKDAY(minDateSelected;1)=2;  minDateSelected - 2; minDateSelected -1)
var newMinDate = ???```

newMinDate has to be newMaxDate - number of different entries in date column in range of workingDaysSelected

Is there any kind of solution for this problem?

Thank you

Helper I

Hi Tom,

this is exactly what I need. I dont know how to get user selected values.

Thank you very much!

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

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

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

#### Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

#### Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors