Forum Discussion

heidibb's avatar
heidibb
Icon for Helper IV rankHelper IV
9 years ago
Solved

Create column with Earlier value from another row

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 fun...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

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

     

    Regards

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

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