Forum Discussion

jlkbi's avatar
jlkbi
New Member
4 years ago
Solved

Creating a YTD Daily Average Measure

Hello! I am having a tough time wrapping my head around the steps necessary to create a DAX measure to capture YTD daily averages with the information below:     Because each month contains...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

jlkbi , You are showing month data here.

If you need avg to daily sum

measures 

Daily Impression = sum(Table[Value]) //or count(Table[Value])

Avg Daily Impression = AverageX(Values('Date'[Date]), [Daily Impression])

 

YTD avg = CALCULATE([Avg Daily Impression],DATESYTD('Date'[Date],"12/31"))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

jlkbi's avatar
jlkbi
New Member
4 years ago

Thank you!

Avg Daily Impression is value from our system. It is displayed monthly, that's correct.  I can use a measure incorporating a Days Measure 

DAY ( EOMONTH ( MIN ( 'Date'[Date] ), 0 ) ) to give me a monthly total. Using this, I can figure out the total transactions for a multi-month period.
 
Using your YTD measure however appears to take all of the transaction totals for each month and divides by the number of months in the YTD. Because each month has a different number of days, this doesn't provide the accurate answer for daily averages (in other words, it treats the daily average from 31 days in January with the same weight as 28 days in February).