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

amitchandak's avatar
amitchandak
Super User
6 years ago

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

  • 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