Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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 !

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.