Creating a YTD Daily Average Measure

[jlkbi]

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 a different number of days, simply dividing by # of MTD isn't correct. Thanks for your assistance!

[Anonymous · Accepted solution]

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.

[jlkbi]

Thanks! This was the missing piece I believe.

Would you know how to modify the Day of Year calculation to account for a Fiscal Year (e.g., Jul - Jun)?

[Anonymous]

Hmm, good question. I think you might want to use the DATESBETWEEN function instead: https://docs.microsoft.com/en-us/dax/datesbetween-function-dax Then specify the start date as your start of fiscal year.

Try something like this:

Day of the Fiscal Year = COUNTROWS(DATESBETWEEN('Dates'[Date].[Date],DATE(2021,7,1),TODAY()))

[TomMartens]

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

[jlkbi]

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).

[amitchandak]

@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]

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).