So I've amended your LoD formula to use DATETRUNC on 'day' rather than DATEPART...and now the 2 views both show the same.
I've only got a bit of time today, so wanted to get you an answer posted, but will have a bit more time tomorrow to go into the why's
Date Trunc LoD Include Query.twbx 181.1 KB
Thanks this is great already!
If you could enlighten me with the why, that'd be even better
1 of 1 people found this helpful
So got a bit more time today...
So there are a couple of things here...Firstly the 'day' key word in DATETRUNC and DATEPART do different things.
In DATETRUNC the datetime is truncated to only be at day level (so it returns a date), whereas 'day' in DATEPART returns the day of the month (so 1,2,3...31), so returns an integer...'weekday' is the keyword for DATEPART which returns the Day of Week (Monday, Tuesday, Wednesday...), this one returns a string
So in your formula, it was adding up all the 1st of the months, all the 2nd of the month...etc. and then displaying these by day of week (so a Monday may consist of month day 4, 9, 24...etc.). If we change the formula to be 'weekday' (keeping the DATEPART) that will create a SUM of all the Monday's, all the Tuesday's...etc. which means that when we look at this by DayOfWeek, it's just the same as the standard SUM...so here the LoD has summed up all the Monday visitors (129,500) and then taken an average of this (129,500)
When we use DATETRUNC all this does is roll the data up...if we have our data by hour, say, it aggregates it all to day, like the below (and would also add up each ID within that...so we get a single returned value for each day, even if that day is made up of multiple rows)
This means when we get the SUM per day...once we have this, taking the average gives us the Average Daily sales, and then including the DayOfWeek we get this split by Monday, Tuesday....
Hope that helps, but let me know if anything doesn't make sense.
Great man! You've made it crystal clear to me
You're a legend, thanks
All the best,