cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarcS
Helper I
Helper I

Compare selected time period (filter) with non blank time period before

Hello,

 

Iam looking for a solution for following problem:

Given table below:

 

DateHourReachmoremoremore
16.06.20174125xxx
16.06.2017585xxx
16.06.2017645xxx
16.06.20177189xxx
17.06.20177450xxx
17.06.20178384xxx
19.06.20177142xxx
19.06.2017812xxx
19.06.2017945xxx
20.06.2017468xxx
20.06.2017574xxx
20.06.20176125xxx
20.06.20177120xxx
21.06.20178241xxx
21.06.2017935xxx
21.06.201710214xxx
25.06.20174120xxx
25.06.20175189xxx
26.06.20174171xxx
27.06.2017836xxx


1. Example

If the user selects a time period for example 25.06-27.06 -> reach would be 516.
Now I want to show the reach before, for the same nonBlank time period.

So DISTINCTCOUNT(date) for selecte values = 3 (25.06 & 26.06 & 27.06) 
Reach time periode before = 1.076 (19.06 & 20.06 & 21.06). NOT 22.06 & 23.06 & 24.06 = 0

2. Example
If the user selects a time period for example only the 19.06 -> reach would be 199.
DISTINCTCOUNT(date) for selecte values = 1 (19.06) 
Reach time periode before = 834 (17.06). NOT 18.06 = 0

3. Example
If the user selects a time period for example 20.06 & 21.06 -> reach would be 877.
DISTINCTCOUNT(date) for selecte values = 2 (20.06 & 21.06) 
Reach time periode before = 988 (17.06 & 19.06). NOT 19.06 & 18.06 = 199

With the help of TomMartens I got this solution for a new measure:

reachBefore = 
var minDateSelection = MINX('statistics';'statistics'[Date])
var numberOfDatesInSelection = DISTINCTCOUNT('statistic'[date])
return

calculate(
	SUM(statistic[reach]);	
	topn(numberOfDatesInSelection;
		FILTER(ALL(statistics);
			'statistics'[Date] <  minDateSelection
		);
	'statistics'[Date];
	DESC
	)
)



But this doesnt work correct. I there any solution? 

Thanks

2 ACCEPTED SOLUTIONS
MarcS
Helper I
Helper I

I think now its working:

reachBefore =
var minDateSelection = MINX('statistics';'statistics'[Date])
var numberOfDatesInSelection = DISTINCTCOUNT('statistic'[date])
return

calculate(
SUM(statistics[reach]);
topn(numberOfDatesInSelection;
FILTER(ALL(statistics[date]);
'statistics'[Date] < minDateSelection
);
'statistics'[Date];
DESC
)
)

I dont know why, maybe somebody can explain why Filter(All(statistics);... not working | Filter(All(statistic[date]);... working

Best regards

View solution in original post

Ok for me it is working in any cases because I do not have any other filters coming from Statistics Table.

 

When you write All(Statistics) you are actually ignoring (or to be more precise overriding) all the filters coming from Statistics Table (e.g all the columns from Statistics Table) whereas All(Statistics[Dates]) only ignores the filter(s) coming from the Dates Column of your Statistics Table.

 

Slicers, visual-level, page-level or report-level filters coming from any other columns than [Dates] may explain your differences !

View solution in original post

4 REPLIES 4
MarcS
Helper I
Helper I

I think now its working:

reachBefore =
var minDateSelection = MINX('statistics';'statistics'[Date])
var numberOfDatesInSelection = DISTINCTCOUNT('statistic'[date])
return

calculate(
SUM(statistics[reach]);
topn(numberOfDatesInSelection;
FILTER(ALL(statistics[date]);
'statistics'[Date] < minDateSelection
);
'statistics'[Date];
DESC
)
)

I dont know why, maybe somebody can explain why Filter(All(statistics);... not working | Filter(All(statistic[date]);... working

Best regards

View solution in original post

Ok for me it is working in any cases because I do not have any other filters coming from Statistics Table.

 

When you write All(Statistics) you are actually ignoring (or to be more precise overriding) all the filters coming from Statistics Table (e.g all the columns from Statistics Table) whereas All(Statistics[Dates]) only ignores the filter(s) coming from the Dates Column of your Statistics Table.

 

Slicers, visual-level, page-level or report-level filters coming from any other columns than [Dates] may explain your differences !

View solution in original post

Hi @MarcS

 

Nice example. The logic of the formula you are using is good and It is working for me.

Would you give us an example where it doesn't compute what you expect ?

 

A simple amendment for your mindateselection variable,

Var mindateselection = min('Statistics'[Date]) is simpler 🙂 

Hi, thanks for the answer.
I tried this measure with following date period:

20-21.06 reach: 877 (correct)
reachBeforeSelection: 199 (expected 988) not correct

19.06 reach: 199 (correct)
reachBeforeSelection: 834 (expected 834)

25-26-27.06 reach: 516 (correct)
reachBeforeSelection: 490 (expected 1076) not correct

25.06 reach: 309 (correct)
reachBeforeSelection: 490 (expected 490)

26.06 reach: 171 (correct)
reachBeforeSelection: 309 (expected 309)

So sometimes it works well, sometimes not?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

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.

R2 (Green) 768 x 460px.png

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