Link to home
Start Free TrialLog in
Avatar of SQL Power BI
SQL Power BIFlag for United States of America

asked on

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.

User generated image

Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

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

User generated image
Avatar of SQL Power BI

ASKER

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.
Avatar of Norie
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?

User generated image

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 )

Open in new window

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)
 
User generated image
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 ])
 
 
 
User generated image
Can anyone please help?
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])

User generated image 
 
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
User generated image
What should the answer be in Average # of Days 10?  And what is the significance of 10?

There is no significance of 10 but it's just a measure name as I have created many measures with name 1, 2 3 and so on.
The answer for the Average # of Days 10  should be  =  The average difference between Registration Date and Start Date. So for all users the difference between the Start Date minus the Registration Date. So every row in the screen shot should show a number displaying the average difference between the Start date minus the Registration Date. Please don't look at the duration which is correct but I can't figure out the Average difference between the start date minus the registration date?
Any ideas and codes to fix this? It has been taking long time and no experts here can help.
I look back through our comments and it feels like we are experiencing "Ground Hog Day".  If you look at each line in your example the duration is the average, the average of one row.  If you think it should be a different number, then like I have asked before, what should that numbers be in your example?  What should show up for each line below?