How to plot monthly average of number of days from calculated distance between two dates?

[another1here]

Hi - I'd love to know if there's anybody that can help guide me with how to generate a line chart with points at the month marks, which represent the average of all numbers for any given month. However, the numbers that need to be averaged would need to be calculated numbers themselves.

Real-world example: I need to understand the monthly trend of amount of time that a candidate is in the interview process from the time the individual applies for the job to the time they are closed out. Two deeper levels of detail would be: [1] view this by team [2] view only the average (by month) who had the outcome "Hired". 

In other words, I'd like to be able to say: "In January, the average days it took a candidate to get from Applied to Closed was 28 days; In February, it was 25 days; In March it was 29 days..." and to see this on a line chart with those numbers representing the monthly trend.

Data for example above is attached below. 

Column F would need to be the calculation of days from "Applied" to "Closed" ( =[Closed]-[Applied]). This is easy, I can set up the calculation in that column from the raw data, but I don't want to do that at this step if there's an simpler way to do everything I'm trying to do here with monthly averages.

How do I do this in PowerBI Desktop so that the days for each candidate are calculated and then I can take an average *by month* to then show it on a line graph, and be further able to break it down by Team and/or Outcome type?

Thank you in advance for any guidance you can give me.

[Greg_Deckler · Accepted solution]

@another1here - So, I created the following columns:

DaysToClose = DATEDIFF([Applied],[Closed],DAY)

MonthClosedSort = MONTH([Closed])

MonthClosed = FORMAT([Closed],"mmmm")

And then the following measure:

AverageToClose = AVERAGE([DaysToClose])

I set MonthClosed to have a Sort By of MonthClosedSort. 

I created a visual with MonthClosed as the Axis and AverageToClose for Values. Then, you could choose either your Org or your Outcome as a Legend and have the other one as a slicer for example.

[jessewysong]

This makes sense, and I have very similar data. I am looking for a count (and average) of the total days between the date a customer was setup and when they had thier first invoice. I am getting an odd error message. I would appreciate any help, as I dont appear to have datediff_big in my formula? I am wondering if I have my columns or my data classified incorrectly?

Thank you. 

[another1here]

@Greg_Deckler This is fantastic. Thank you so much!

Question for you - this shows months in order from January to December, regardless of year. If I have data that spans different years (for instance, previous 6 months from today's date, which would include both 2015 and 2016), how can I show it it chronological order? I'd want the visual to show 2015 October first (as the first date) and so on, with March 2016 at the end of the axis (for today). 

I realize this is probably a simple question, I figure I'm probably not thinking about something basic :). 

Thanks in advance for your help!

[Greg_Deckler]

@another1here - Just change MonthCloseSort to something like:

MonthClosedSort = CONCATENATE(YEAR([Closed]),MONTH([Closed]))

[another1here]

Awesome. Thank you again! You have been super helpful. I really appreciate the assistance! 

[another1here]

One more thing I'll add for the benefit of the community if this helps anyone else-

I ended up with a single digit month for months 1 through 9, which threw it out of order. So to create two-digit months, I created another new column 

MonthTwoDigit = FORMAT(table_name[Closed],"mm")

and then changed MonthClosedSort to

MonthClosedSort = CONCATENATE(YEAR([Closed]),table_name[MonthTwoDigit])

That did the trick for me. Although maybe there was a better/easier way to do it... but this worked. 

[asocorro]

I would do just what you say, calculate the elapsed number of days for each column, and then create a measure to calculate average the number of days.  I think you will also need a Month column for the chart's axis.