Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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)?
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:
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
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |