How to get value for every date from dataset with wide range of dates.

[OliHns]

Hi,

So I've got a dataset that has emails sent over various date ranges for every member of staff. (An export from MS Admin centre.) The dataset looks like with StartDate and Emails/Day as calculated columns.

User	Emails Sent	Date	Period	StartDate	Emails/Day
A	100	21/10/21	7	14/10/21	14.3
B	150	21/10/21	7	14/10/21	21.4
C	120	21/10/21	7	14/10/21	17.1
A	300	21/10/21	30	21/09/21	10
B	400	21/10/21	30	21/09/21	13.3
C	350	21/10/21	30	21/09/21	11.7
A	80	15/10/21	7	08/10/21	11.4
B	50	15/10/21	7	08/10/21	7.1
C	120	15/10/21	7	08/10/21	17.1

I then also have two referenece tables, a full Staff List with simply users A, B, C etc. their associated departments/regions, and a Dates table with 01/01/21, 02/01/21, etc. and the correlated week, month, year those dates fit.

So the question is how do I write a measure to tell me the estimated/calculated number of emails sent on a day/week/month by a User/Department/Region?

I've been trying to write a Measure attached to the Dates table something like = CALCULATE(AVERAGE(Emails[Sent/Day]),Dates[Date]>=Emails[StartDate],Dates[Date]<=Emails[Date]) but I can't seem to do this more than or less than filter. I assume I need to put in a relationship between the Emails and Dates tables somewhere but I can't work out if it should reference off of the Date or StartDate column.

Any advice/help would be much appreciated!

Thanks.

[amitchandak · Accepted solution]

@OliHns , refer the file or the blog can help

or can date diff help ?

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...