# Power BI - DAX how to get the Average days between two dates?

Unable to solve the Average days issue using DAX. Here is the example.

## Average date between two dates in Power BI using DAX not working.

Is there a way to solve this using DAX?

? Average days between Registration Date and Start Date (For all user IDs the difference between Start Date minus Registration Date.

Tom Farrar

Can you provide example answers for the 3 lines in you question?

I need the third column in the image about as "Number of Days" by calculation the Average of number of days between the Start date and Registration date for a given user id. What was the average number of days when it took a given user to register for a course and when they started the course.
Norie

Where's the ID column?

User id's are emails I can't share but assume that each user is taking a course or multiple users taking the same course. But I'm trying to find out the Average number of days using DAX based on when it took a given user to register for a course and when they started the course but I need the Average number of days as third column.

This is really urgent.
Thank you

I tried this but it didn't work.

Average Duration = AVERAGEX(‘Table1, DATEDIFF('Table1'[Registration Date], RELATED('Table2'[Start Date]), DAY))

Average Duration = AVERAGEX(‘Table1', DATEDIFF('Table1'[Registration Date], RELATED('Table2'[Start Date]), DAY))

Any ideas?

The DATEDIFF works on both the Registration Date and start Date but the AVERAGE Does not work.
How can there be an average for a given user id? For one user the difference is what it is?  That is why I asked for an example.

I just need the Average days between Registration Date and Start Date. Do you have some code in DAX etc to do that?

It looks like no Experts here to answer this question?
Are you trying to create a measure or a calculated column?  I think you want a measure, right?

Either works fine
seems this question is a duplicate.
Here is an example that works if I understand your requirement.
EEExample.pbix

@Tom

Your example gives the same result. Here is the result screen shot from your Power Bi File. I want the Average to show up in every row instead of Total at the end?

You want the average 8.5 to show up in every row of the table, or report?  EEExample.pbix

Every row of the table.
Then you need to create a calculated column within the table.  Right click the table name and select "New Column".  Then add this code:

AvgDayDiff =
AVERAGEX(
All(Table1), DATEDIFF(Table1[Date], RELATED(Table2[Date]),DAY))

EEExample.pbix

When I click add Calculated Column it brings up the M Code Window. I'm not sure what other ways. Your code repeats same for all row but it should be different based on those different dates.

If you expect tp get a different answer for each row in the table, then that answer is simply subtracting one column from the other.  I added a column in the table that gives you the average of all the differences, and would not understand what you are looking for without a specific example showing the two columns (apparently in different tables) and the resultant column with the values you are expecting.
As for why you are getting the M code window, I expect you are in the Power Query functionality of Power BI.  That is all I can think of.

I also tried something like this but it does not return anything. I created a measure.

Avg # of Days =
``DATEADD(    LASTDATE([Start Date]),    DATEDIFF(        LASTDATE([Start Date]),        LASTDATE([Registration Date]),        DAY    )/2,    DAY )``

How can I get the Avg # of days to show a digit instead of a whole date? Here is the screen shot and DAX

Duration = DATEDIFF('Table1'[Registration Date], RELATED('Table2'[Start Date]), DAY)

Average # of Days  = DATEADD(LASTDATE('Table1'[Registration Date]), DATEDIFF(LASTDATE( 'Table1'[Registration Date]),LASTDATE('Table2'[Start Date] ),DAY)/2,DAY)

I also changed the Average # of Days Column to text, short date, date but I still get the whole date instead of a number as an Average. Any ideas?

Any Experts out there? Any ideas and suggestions?

Why the AVERAGEX provides the same result as DATEDIFF but not the Average number of Days?

Duration = DATEDIFF(Table1'[Registration Date], RELATED(‘Table2’[Start Date]), DAY)

Average # of Days  = AVERAGEX('Table1'[Registration Date]) - RELATED('Table2'[Start Date ])

Never saw the screen shot.  Still would like to see a simple example...
Could try this formula---

Average Duration =
AVERAGEX(‘Table1,
'Table1'[Registration Date] - RELATED('Table2'[Start Date])

It gives the same values as Duration with DATEDIFF,

Duration = DATEDIFF(Table1'[Registration Date], RELATED(‘Table2’[Start Date]), DAY)
Average # of Days 10 =
AVERAGEX(‘Table1,
'Table1'[Registration Date] - RELATED('Table2'[Start Date])

What value are you expecting in the Duration column?

Duration column values are correct but not the Average # of Days 10. The following is not returning for every row but total. Also why it's the same value as duration?

Average # of Days 10 =
AVERAGEX(‘Table1,
'Table1'[Registration Date] - RELATED('Table2'[Start Date])
Where are you creating the formula?  Are you creating a measure for it?

Yes I'm creating a measure.
You said:
"Duration column values are correct but not the Average # of Days 10. The following is not returning for every row but total. Also why it's the same value as duration?

Average # of Days 10 =
AVERAGEX(‘Table1,
'Table1'[Registration Date] - RELATED('Table2'[Start Date])"

My question again is:
In the example data you provided

What should the answer be in Average # of Days 10?  And what is the significance of 10?