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.
Hi All, I am a beginner and working on a visual. The problem is best illustrated by the following table.
Date | Value | Required Avg Value |
4/1/2019 | 342 | Avg of 342, 298 |
3/1/2019 | 354 | Avg of 354, 298 |
2/1/2019 | 308 | Avg of 308, 298 |
1/1/2019 | 298 | Avg of 298, 298 |
3/1/2018 | 344 | Avg of 344, 306 |
2/1/2018 | 322 | Avg of 322, 306 |
1/1/2018 | 306 | Avg of 306, 306 |
The resulting column should give an average of selected row value and the value for the beginning of the year. For 2019, the year beginning value is the value for 1/1/2019 and for 2018, the value is for 1/1/2018.
I added column in Data View extracting the year of the row and tried using it the LOOKUPVALUE function -
=LOOKUPVALUE([Value], [DATE],DATE (Year (extracted value), 1, 1)
It appears that I cannot use the value from another column in the Search value function.
Please advise me how I can solve this problem.
Thanks in advance.
Solved! Go to Solution.
Hello @krishnak ,
You may try this:
Answer =
VAR _YearStart =
LOOKUPVALUE (
Sheet2[Value],
Sheet2[Date], DATE ( YEAR ( Sheet2[Date] ), 1, 1 )
)
VAR _Current = Sheet2[Value]
VAR _Avg =
DIVIDE ( _YearStart + _Current, 2 )
RETURN
_Avg
Result
Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
https://www.vivran.in/
Connect on LinkedIn
@krishnak , to me it seems like YTD, you have measures like below using time intelligence and calendar
YTD QTY = TOTALYTD(Average('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Average('order'[Qty]),dateadd('Date'[Date],-1,year))
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
@krishnak it should be following measures
Avg = AVERAGE ( Table[Value] )
Avg Start of the Year =
VAR __dateStartOfTheYear = DATE ( YEAR ( MAX( 'Table'[Date] ) ), 1, 1 )
RETURN
CALCULATE ( [Avg], ALL ( 'Table' ), 'Table'[Date] = __dateStartOfTheYear )
Output
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you Vivek and others for looking at my problem and offering solutions.
Now I realize that I have other issues in the table to be resolved - like the day in January of each year is not always the 1st - sometimes it is the 3rd or 4th. That is another issue I have to sort out.
Thank you all for the prompt response - you are awesome!
Krishna
Hello @krishnak ,
You may try this:
Answer =
VAR _YearStart =
LOOKUPVALUE (
Sheet2[Value],
Sheet2[Date], DATE ( YEAR ( Sheet2[Date] ), 1, 1 )
)
VAR _Current = Sheet2[Value]
VAR _Avg =
DIVIDE ( _YearStart + _Current, 2 )
RETURN
_Avg
Result
Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
https://www.vivran.in/
Connect on LinkedIn
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |