Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to calculate the number of occurrences of the same event and product in the last 7 days. Below is a table to illustrate:
ID | PRODUCT | EVENT | DATE | OCCURRENCES IN LAST 7 DAYS |
1 | A | 1 | 20/10/2020 | 2 (ID 1 and 3) |
2 | A | 2 | 17/10/2020 | 1 |
3 | A | 1 | 14/10/2020 | 2 (ID 3 and 4) |
4 | A | 1 | 11/10/2020 | 1 |
5 | B | 1 | 18/10/2020 | 1 |
6 | B | 3 | 16/10/2020 | 2 (ID 6 and 7) |
7 | B | 3 | 15/10/2020 | 1 |
8 | C | 3 | 18/10/2020 | 1 |
I've tried several measures, but I'm not having success. can anybody help me?
Solved! Go to Solution.
@StrikerZero this code might be what you want
OCCURRENCES IN LAST 7 DAYS:=CALCULATE(COUNT(EventsTable[EVENT]),DATESINPERIOD(EventsTable[DATE],MAX(EventsTable[DATE]),-7,DAY),ALLEXCEPT(EventsTable,EventsTable[PRODUCT],EventsTable[EVENT]))
@StrikerZero you can try this formula .
@StrikerZero this code might be what you want
OCCURRENCES IN LAST 7 DAYS:=CALCULATE(COUNT(EventsTable[EVENT]),DATESINPERIOD(EventsTable[DATE],MAX(EventsTable[DATE]),-7,DAY),ALLEXCEPT(EventsTable,EventsTable[PRODUCT],EventsTable[EVENT]))
The @wdx223_Daniel solution is perfect.
Sorry for the delay in responding, I had to make an emergency trip.
Before marking the answer to the end of the topic, could you show me how to drill through or filter these items from the occurrence? For example, if it is shown that an event had 3 occurrences, what were those occurrences?
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
13 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |