Forum Discussion
DATEDIFF - Unexpected Results
Hi,
Trying to calculate the average age from all the DOB in the table. (pbix below)
1st date is the [dob] in one table and the second date [MaxDateofWeek] is from a calculated column in another table that returns the date of the last day of each week.
pbix file -> pbix file
AlexisOlson Thank you very much for your advice.
You measure works perfectly.
2 Replies
One issue is that there is no relationship between the date table and your other tables, so averaging the Age column is not affected by the Week in your visual. Even if there were a relationship, [Age] is a calculated column, which cannot be affected by filter context in your visual. Another wrinkle is that, DATEDIFF(..., YEAR) is always an integer value, so you can expect the same average for every week in a particular year.
I'd propose a measure more like this if you expect age to change between weeks:
AvgAge = AVERAGEX ( 'powerbi114 client', DATEDIFF ( 'powerbi114 client'[dob], MAX ( 'Date'[Date] ), DAY ) ) / 365AlexisOlson Thank you very much for your advice.
You measure works perfectly.
