Getting an average date of occurrence over several years

[rhborders]

I am trying to get an average and standard deviation for the date an invoice is registered over several years. I currently have the dates grouped by concateinating custumer ID, product ID, and salesrep ID. To originally solve this I converted the date to a Julian date and then averaged that number. I realized this would not work and I am now wondering if it is at all possible to find the average day/month that the invoice occured based on past years data.

If more information is needed, don't hesitate to ask.

Thanks!

[Anonymous]

Assuming there's only one invoice per year per, let's say, customer, you're looking for the "average" date that this falls on, right? Or potentially by month? Or is it average invoice for a particular combination of customer, product, and sales rep?

Basically, all you'll need to do is convert your dates to a date integer, typically `YYYYMMDD` in numbers, and then you can go from there; in the case of finding the average "day" per month, you can simply average the counts by the day number (although you'd technically want to divide by something like 30.4 instead of 30/31).

[rhborders]

I had tried using taking the YYYYMMDD date format and converting it to number and then averaging, but the issue is that I am trying to show this as a forecast as to when to expect an invoice to be done by and averaging it this way gives me an old year. My current solution is to right a long if statement that converts the month/day to a workable number representing a day in a year from 1-365. This seems tedious to solve the job so if you can think of a faster way of doing so I am open to trying something different.

[Anonymous · Accepted solution]

If you need the day number of the year, do a DATEDIFF between that date and the first day of that year. Check out Eric's solution in this thread: https://community.powerbi.com/t5/Desktop/Day-number-of-year/td-p/55688

[rhborders]

That will do the job. I ended up making the IF function anyways, but I think that I will still need the IF function in order to convert the day number of the year to MONTH/DAY.

Thank you!

[rhborders]

That will do the job. I ended up making the IF function anyways, but I think that I will still need the IF function in order to convert the day number of the year to MONTH/DAY.

Thank you!