Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |