Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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 !
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 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |