Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
29 | |
28 | |
12 | |
12 | |
12 |