cancel
Showing results for
Did you mean:
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:

 Date Hour Reach more more more 16.06.2017 4 125 x x x 16.06.2017 5 85 x x x 16.06.2017 6 45 x x x 16.06.2017 7 189 x x x 17.06.2017 7 450 x x x 17.06.2017 8 384 x x x 19.06.2017 7 142 x x x 19.06.2017 8 12 x x x 19.06.2017 9 45 x x x 20.06.2017 4 68 x x x 20.06.2017 5 74 x x x 20.06.2017 6 125 x x x 20.06.2017 7 120 x x x 21.06.2017 8 241 x x x 21.06.2017 9 35 x x x 21.06.2017 10 214 x x x 25.06.2017 4 120 x x x 25.06.2017 5 189 x x x 26.06.2017 4 171 x x x 27.06.2017 8 36 x x x

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

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 !

4 REPLIES 4
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
MVP

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 !

MVP

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 🙂

Helper I
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?

Announcements

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

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

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

#### 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
Top Kudoed Authors