Forum Discussion
Helper INeed help with YTD Average for static data
I have a list of average temperatures from 1991-2020 for two weather stations representing "Normal" weather. I need help writing a measure that will reflect the total average through the current month selected in the current year.
The following measure calculates the average for the entire year:
Here is what my visual reflects even though I only want data through August (because as of today September is not yet available) and I have a month filter set to August, a year filter set to 2022 and station filter is set to "Airport". This value reflects Jan-Dec. (Hi Temp is Month_Max in the data table)
What I need to reflect are the highlighted values when the station filter is applied, current year filter is selected and the month of August is selected:
Help is greatly appreciated!
10 Replies
- parry2k
Super User
CMccown try this:
NormHiTemp = Averagex(VALUES('Normal'[Month]),CALCULATE( Average('Normal'[Month_Max])))✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
- parry2k
CMccown I see, do you have a date dimension in your model? How your model look like?
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
CMccownFor this particular table the only dimension is "month" due to the fact that the data is aggregated. Here is a screen shot of the model:
- parry2k
CMccown I would recommend changing the month column in the normal table to the last date of the month and then having direct relationship direct with the date dimension and from there YTD calculation will be easy using a time intelligence function like DATESYTD
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
- CMccown
I will give that a try! Thank you for your help.
- Ashish_Mathur
Hi,
Share the link from where i can download your PBI file. Ensure that there is a Year column as well in the source dataset (I do not see it in the first image that you have shared in your original post).
- parry2k
Ashish_Mathur appreciate your reply but don't understand the relevance of your question. Can you elaborate a bit more? Thank you!
- parry2k
Ashish_Mathur you don't have to reply if you don't have anything new to add, but if you feel you are adding value to the post then it all makes sense. Keep up the good work.
- CMccown
I found a DAX calculation that worked. After creating a calculated column in the 'Normal' table to refelct the latest month (based on latest date) in the 'Actual' table, I created the following measure:
YTDPartNormHiTemp = CALCULATE('Normal'[NormAvgHi], FILTER('normal', ('Normal'[Month]<= 'Normal'[LatestDataMonth])))Thank you for your help. Your suggestions prompted my search with a different perspective which helped me land here.
