Average occurrences across a date range

[Mariposa14]

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: 

Frequency = COUNTX(

    FILTER('Test data', EARLIER('Test data'[ID]) = 'Test data'[ID]), 'Test data'[ID]

)

Which gives me this result:

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:

Average frequency = averagex(values('Test data'[ID]),calculate(AVERAGE('Test data'[Frequency])))

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!

[v-tangjie-msft · Accepted solution]

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. 

[FreemanZ]

 @Mariposa14 

could you also provide your expected result?