Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |