Forum Discussion

jhollingworth's avatar
jhollingworth
Frequent Visitor
2 years ago
Solved

Lookup value in same table, matching to previous date

Hello,

I have a problem that I thought was going to be an easy fix. Unfortunately, I can't seem to do it. I think I'm making it more complicated than it needs to be.

 

I have a simple table like below, but it contains thousands of lines with multiple PHARMACY_IDs. What I would like to do is create a calculated column that returns the Value for the previous date, matching all other columns.

 

340B_IDPHARMACY_IDDateManufacturerAttributeValue
DSH19006418856/18/2024abbvieRETAIL_STATUSINELIGIBLE
DSH19006418856/18/2024abbvieDUOPA_STATUSINELIGIBLE
DSH19006418856/18/2024abbvieIMBRUVICA_STATUSINELIGIBLE
DSH19006418856/18/2024abbvieVENCLEXTA_STATUSINELIGIBLE
DSH19006418857/1/2024abbvieRETAIL_STATUSELIGIBLE
DSH19006418857/1/2024abbvieDUOPA_STATUSELIGIBLE
DSH19006418857/1/2024abbvieIMBRUVICA_STATUSELIGIBLE
DSH19006418857/1/2024abbvieVENCLEXTA_STATUSELIGIBLE

 

Wanted results:

340B_IDPHARMACY_IDDateManufacturerAttributeValuePrevious Value
DSH19006418856/18/2024abbvieRETAIL_STATUSINELIGIBLE 
DSH19006418856/18/2024abbvieDUOPA_STATUSINELIGIBLE 
DSH19006418856/18/2024abbvieIMBRUVICA_STATUSINELIGIBLE 
DSH19006418856/18/2024abbvieVENCLEXTA_STATUSINELIGIBLE 
DSH19006418857/1/2024abbvieRETAIL_STATUSELIGIBLEINELIGIBLE
DSH19006418857/1/2024abbvieDUOPA_STATUSELIGIBLEINELIGIBLE
DSH19006418857/1/2024abbvieIMBRUVICA_STATUSELIGIBLEINELIGIBLE
DSH19006418857/1/2024abbvieVENCLEXTA_STATUSELIGIBLEINELIGIBLE

 

 

Below is the dax code I am using, and the results are all blank. There is a relationship between the date column and a date table.

 
Previous Value =
VAR _prevdate = OFFSET(-1, ORDERBY('ESP Eligibility Downloads'[Date]))

RETURN
LOOKUPVALUE('ESP Eligibility Downloads'[Value],
'ESP Eligibility Downloads'[340B_ID], 'ESP Eligibility Downloads'[340B_ID],
'ESP Eligibility Downloads'[Manufacturer], 'ESP Eligibility Downloads'[Manufacturer],
'ESP Eligibility Downloads'[PHARMACY_ID], 'ESP Eligibility Downloads'[PHARMACY_ID],
'ESP Eligibility Downloads'[Attribute], 'ESP Eligibility Downloads'[Attribute],
'ESP Eligibility Downloads'[Date], CALCULATE(MAX('ESP Eligibility Downloads'[Date]), _prevdate)
)

Results:

 

 
 
Any insight is appreciated!
Thank you!

 

Need Help
  • NaveenGandhi's avatar
    NaveenGandhi
    2 years ago

    Hi jhollingworth 

    If you wanna do it as a measure, Try this.

     

    Previous Value =
    VAR CurrentDate = MAX('Table'[Date])
    RETURN
        CALCULATE(
            MAX('Table'[Value]),
            FILTER(
                ALL('Table'),
                'Table'[Date] < CurrentDate
            )
        )
     If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

4 Replies

  • jhollingworth's avatar
    jhollingworth
    Frequent Visitor
    2 years ago

    Thank you everyone for the help. The measure work posted by NaveenGandhi. The two calculated columns both resulted in blank columns. I will play around with those.

    Thanks again.

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi jhollingworth - Create a calculated column as below to that returns the Value for the previous date.

     

    Snap

     

    Create a new calculated column FYR: Change the table name as per your dataset references.

     

    Previous Value =
    VAR Current340B_ID = 'Looku'[340B_ID]
    VAR CurrentPHARMACY_ID = 'Looku'[PHARMACY_ID]
    VAR CurrentDate = 'Looku'[Date]
    VAR CurrentManufacturer = 'Looku'[Manufacturer]
    VAR CurrentAttribute = 'Looku'[Attribute]

    VAR PreviousDate =
        CALCULATE(
            MAX('Looku'[Date]),
            FILTER(
                'Looku',
                'Looku'[340B_ID] = Current340B_ID &&
                'Looku'[PHARMACY_ID] = CurrentPHARMACY_ID &&
                'Looku'[Manufacturer] = CurrentManufacturer &&
                'Looku'[Attribute] = CurrentAttribute &&
                'Looku'[Date] < CurrentDate
            )
        )

    RETURN
        CALCULATE(
            MAX('Looku'[Value]),
            FILTER(
                'Looku',
                'Looku'[340B_ID] = Current340B_ID &&
                'Looku'[PHARMACY_ID] = CurrentPHARMACY_ID &&
                'Looku'[Manufacturer] = CurrentManufacturer &&
                'Looku'[Attribute] = CurrentAttribute &&
                'Looku'[Date] = PreviousDate
            )
        )
     
    Hope it works
     
    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!
  • NaveenGandhi's avatar
    NaveenGandhi
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Hi jhollingworth 

    If you wanna do it as a measure, Try this.

     

    Previous Value =
    VAR CurrentDate = MAX('Table'[Date])
    RETURN
        CALCULATE(
            MAX('Table'[Value]),
            FILTER(
                ALL('Table'),
                'Table'[Date] < CurrentDate
            )
        )
     If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    This calculated column formula works

    Column = LOOKUPVALUE(Data[Value],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[340B_ID]=EARLIER(Data[340B_ID])&&Data[PHARMACY_ID]=EARLIER(Data[PHARMACY_ID])&&Data[Date]<EARLIER(Data[Date])&&Data[Manufacturer]=EARLIER(Data[Manufacturer])&&Data[Attribute]=EARLIER(Data[Attribute]))),Data[340B_ID],Data[340B_ID],Data[PHARMACY_ID],Data[PHARMACY_ID],Data[Manufacturer],Data[Manufacturer],Data[Attribute],Data[Attribute])

    Hope this helps.