cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarcS
Helper I
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
TomMartens
Super User II
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

2017-07-05_19-32-04.png

 

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

View solution in original post

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

View solution in original post

17 REPLIES 17
TomMartens
Super User II
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

2017-07-05_19-32-04.png

 

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

View solution in original post

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.2017178
22.02.20173839
22.02.20174706
22.02.20175913
22.02.20176487
23.02.20173430
23.02.20174675
23.02.20175165
25.02.20173747
25.02.20174704
25.02.20175550
25.02.20176906
26.02.2017110
26.02.20172657
26.02.20173856
27.02.20179323
27.02.201710475
27.02.201711691

 

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

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

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

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.

 

2017-07-05_19-32-04.png

 

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

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

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

Yes that it is;)

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

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

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

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

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

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

View solution in original post

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.

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

Hi Tom,

 

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

 

Thank you very much!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

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

Top Solution Authors