Create column with Earlier value from another row

[heidibb]

Hello,

I have a data table similar to this (the first three columns). I would like to create a column that pulls in the previous week's date. I think from my research I should use the EARLIER function, but I'm not sure how to structure the formula. So, for each ID and week, I need to get the date for the previous week for the same ID.

Any thoughts?

[v-ljerr-msft · Accepted solution]

Hi @heidibb,

If I understand you correctly, you should be able to use the formula below to create a column that pulls in the previous week's date in your scenario. 

Previous Week Date = 
CALCULATE (
    MAX ( Table1[Date] ),
    FILTER (
        ALL ( Table1 ),
        Table1[ID] = EARLIER ( Table1[ID] )
            && Table1[Week]
                = EARLIER ( Table1[Week] ) - 1
    )
)

Regards

[TomMartens]

Hey,

here is a part of my calendar already with the column you want to create "prevDate"

The column "Month Year" corresponds to your column "ID" and the column "Day of Month" to your column "Week" and here is the DAX statement

prevDate = 
var currentGroup = calculate(max('Calendar'[Month Year]))
var currentDayIndex = calculate(max('Calendar'[Day Of Month]))
return
LOOKUPVALUE('Calendar'[Date], 'Calendar'[Month Year], currentGroup, 'Calendar'[Day Of Month], currentDayIndex -1)

I'm storing the column values of the current row into variables and then use these variables inside the LOOKUPVALUE function to retrieve the Date column by adjusting the currentDayIndex value with -1.

Hope this helps

Regards

[heidibb]

As I continue to research, I wonder if LookupValue is a better function. Still unsure how to structure the formula since i need the previous week's date at the ID level.