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

[MarcS]

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

[TomMartens · Accepted solution]

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 ...

[TomMartens · Accepted solution]

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 ...

[MarcS]

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

[TomMartens]

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

[MarcS]

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

[TomMartens]

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.

Once again, I changed the pbix file please redownload.
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

[MarcS]

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

[TomMartens]

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.

[MarcS]

Yes that it is;)

[TomMartens]

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

[MarcS]

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

[TomMartens]

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 🙂

[TomMartens]

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

[MarcS]

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

[TomMartens · Accepted solution]

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)

[Anonymous]

@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.

[MarcS]

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

[MarcS]

Hi Tom,

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

Thank you very much!