Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rhborders
Helper I
Helper I

Getting an average date of occurrence over several years

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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).

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
Not applicable

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

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!

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors