Hi Team,
Looking for some help to do the following...
Data as follows:
<tbody>
</tbody>
I want a DAX measure to return the following results:
<tbody>
</tbody>
Which was computed using standard Excel functions as follows:
Logic used:
Per product, establish the range of interest by determining the first and last weeks where supply occurred (note that the first week is always moved on by 1 to 'correct' the range for calculation purposes). In the case of 'Apple', a count of 16 is returned (weeks 5 through to 20). Now, divided this number by the number of times supply has occurred over this same time period. For Apple, 5 is returned (supply occurred in weeks 7, 9, 15, 18 and 20). Finally, divide 16 by 5 to get the desired result: 3.2.
Hope this is clear and someone can assist.
Cheers,
Matty
Looking for some help to do the following...
Data as follows:
Week | Product | Supply |
---|---|---|
1 | Apple | 0 |
2 | Apple | 0 |
3 | Apple | 0 |
4 | Apple | 5 |
5 | Apple | 0 |
6 | Apple | 0 |
7 | Apple | 10 |
8 | Apple | 0 |
9 | Apple | 10 |
10 | Apple | 0 |
11 | Apple | 0 |
12 | Apple | 0 |
13 | Apple | 0 |
14 | Apple | 0 |
15 | Apple | 5 |
16 | Apple | 0 |
17 | Apple | 0 |
18 | Apple | 5 |
19 | Apple | 0 |
20 | Apple | 10 |
1 | Banana | 20 |
2 | Banana | 0 |
3 | Banana | 20 |
4 | Banana | 20 |
5 | Banana | 0 |
6 | Banana | 30 |
7 | Banana | 0 |
8 | Banana | 0 |
9 | Banana | 0 |
10 | Banana | 20 |
11 | Banana | 20 |
12 | Banana | 0 |
13 | Banana | 0 |
14 | Banana | 0 |
15 | Banana | 0 |
16 | Banana | 0 |
17 | Banana | 0 |
18 | Banana | 0 |
19 | Banana | 0 |
20 | Banana | 0 |
<tbody>
</tbody>
I want a DAX measure to return the following results:
Product | Supply Frequency |
---|---|
Apple | 3.20 |
Banana | 2.00 |
<tbody>
</tbody>
Which was computed using standard Excel functions as follows:
Code:
=COUNTIF(INDEX(C$2:C$41,MATCH(1,((B$2:B$41=E2)*(C$2:C$41>0)),0)+1):INDEX(C$2:C$41,MATCH(2,1/((B$2:B$41=E2)*(C$2:C$41>0)))),">=0")/COUNTIF(INDEX(C$2:C$41,MATCH(1,((B$2:B$41=E2)*(C$2:C$41>0)),0)+1):INDEX(C$2:C$41,MATCH(2,1/((B$2:B$41=E2)*(C$2:C$41>0)))),">0")
Logic used:
Per product, establish the range of interest by determining the first and last weeks where supply occurred (note that the first week is always moved on by 1 to 'correct' the range for calculation purposes). In the case of 'Apple', a count of 16 is returned (weeks 5 through to 20). Now, divided this number by the number of times supply has occurred over this same time period. For Apple, 5 is returned (supply occurred in weeks 7, 9, 15, 18 and 20). Finally, divide 16 by 5 to get the desired result: 3.2.
Hope this is clear and someone can assist.
Cheers,
Matty