Forum Discussion
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...
You want to use the Day of Year calculation from this answer: https://community.powerbi.com/t5/Desktop/Day-number-of-year-DAX/m-p/338657/highlight/true
Then sum your Monthly Avg. Impressions and divide it by the Day of Year calculated result.
Hey jlkbi ,
please provide the business rule you want to apply, another question: Do you use a dedicated Calendar table, or does your data model only consists of one table?
What is "Avg Daily Impression", is it a measure?
I assume the Feb YTD Avg is something divided by 59 (31 days in January + 28 days in February).
Regards,
Tom
Thank you. I have a dedicated calendar table.
Avg Daily Impression is value from our system. 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. In your above example, through FEB, there were 302,186,495 impressions (161,245,415 in 31 Jan days and 140,941,080 in 28 Feb days). Dividing that total by 59 YTD days gets me the correct daily average...I suppose I am stuck on how to then correctly calculate the correct YTD days for any specific period (especially if LEAP years are involved).