- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Average occurrences across a date range
I'm new to Power BI and DAX and have found this forum so helpful in getting started - thanks so much for all the great tips! There is one problem that I'm struggling to resolve and wondered if someone could help me. I have data that looks something like this - there are multiple occurrences of each ID on both the same day and spanning several days.
Date | ID |
01-Jan | 101 |
01-Jan | 102 |
02-Jan | 101 |
02-Jan | 101 |
02-Jan | 103 |
03-Jan | 102 |
03-Jan | 103 |
04-Jan | 104 |
04-Jan | 101 |
05-Jan | 101 |
06-Jan | 102 |
07-Jan | 104 |
I would like to work out the average frequency of each ID over a period of time. So far I have used a calculated column to find the number of times each ID occurs, using the following formula:
Date | ID | Frequency |
01-Jan-23 | 101 | 5 |
01-Jan-23 | 102 | 3 |
02-Jan-23 | 101 | 5 |
02-Jan-23 | 101 | 5 |
02-Jan-23 | 103 | 2 |
03-Jan-23 | 102 | 3 |
03-Jan-23 | 103 | 2 |
04-Jan-23 | 104 | 2 |
04-Jan-23 | 101 | 5 |
05-Jan-23 | 101 | 5 |
06-Jan-23 | 102 | 3 |
07-Jan-23 | 104 | 2 |
I then tried to find the average number of occurrences for each ID using the following formula:
Year | Quarter | Month | Day | Average frequency |
2023 | Qtr 1 | January | 1 | 4 |
2023 | Qtr 1 | January | 2 | 3.5 |
2023 | Qtr 1 | January | 3 | 2.5 |
2023 | Qtr 1 | January | 4 | 3.5 |
2023 | Qtr 1 | January | 5 | 5 |
2023 | Qtr 1 | January | 6 | 3 |
2023 | Qtr 1 | January | 7 | 2 |
This excludes any times when there is more than one occurrence for an ID on the same day (e.g. ID 101 on 2 January); however, it is including occurrences of IDs on different days - e.g. ID 103 is included on both 2 and 3 January.
I would like to include the average number of occurrences for each ID only once, associated with the date of the first occurrence. Is this possible?
Many thanks for any suggestions!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Mariposa14 ,
According to your case requirements, you have successfully calculated the count value of the frequency of the deduplicated ID so far, but the problem you are currently experiencing is that you only want to find the first occurrence of each ID into the count, and the rest should be excluded, for your needs, I have done some local tests, I think you can refer to this possible solution
Start by creating a calculated column.
Flag =
var _firstdaete=CALCULATE(MIN('Test data'[Date]),FILTER('Test data','Test data'[ID]=EARLIER('Test data'[ID])))
return
IF('Test data'[Date]=_firstdaete,1,0)
Create another measure to calculate the average value of the count that matches your desired ID, grouped by date.
Frequency1 =
CALCULATE(DISTINCTCOUNT('Test data'[ID]),FILTER(ALLSELECTED('Test data'),'Test data'[ID]=MAX('Test data'[ID])))
Then you can create a visual like this to check if it meets your needs.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Mariposa14 ,
According to your case requirements, you have successfully calculated the count value of the frequency of the deduplicated ID so far, but the problem you are currently experiencing is that you only want to find the first occurrence of each ID into the count, and the rest should be excluded, for your needs, I have done some local tests, I think you can refer to this possible solution
Start by creating a calculated column.
Flag =
var _firstdaete=CALCULATE(MIN('Test data'[Date]),FILTER('Test data','Test data'[ID]=EARLIER('Test data'[ID])))
return
IF('Test data'[Date]=_firstdaete,1,0)
Create another measure to calculate the average value of the count that matches your desired ID, grouped by date.
Frequency1 =
CALCULATE(DISTINCTCOUNT('Test data'[ID]),FILTER(ALLSELECTED('Test data'),'Test data'[ID]=MAX('Test data'[ID])))
Then you can create a visual like this to check if it meets your needs.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
06-12-2024 01:47 PM | |||
05-14-2024 03:55 AM | |||
Anonymous
| 01-22-2024 09:59 AM | ||
01-15-2024 06:26 AM | |||
05-13-2023 05:06 AM |
User | Count |
---|---|
128 | |
81 | |
59 | |
57 | |
43 |
User | Count |
---|---|
183 | |
111 | |
82 | |
66 | |
51 |