Look up value help needed

[krishnak]

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.

[amitchandak]

@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.

[parry2k]

@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!

[krishnak]

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

[vivran22 · Accepted 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