Forum Discussion
Look up value help needed
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.
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 _AvgResult
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
4 Replies
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 _AvgResult
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 LinkedInkrishnak , 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
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!
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
