Forum Discussion

krishnak's avatar
krishnak
New Member
6 years ago
Solved

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...
Need Help
  • vivran22's avatar
    vivran22
    6 years ago

    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

parry2k's avatar
parry2k
Super User
6 years ago

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's avatar
krishnak
New Member
6 years ago

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