Forum Discussion

hwoehler's avatar
hwoehler
Helper I
6 years ago
Solved

get previous values per group

Hello everybody, I would like to have the previous values ​​(see screenshot) displayed for the "ChangeWeek" column. So there should be a new column (see screenshot "new"), where the previous value p...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try , as new columns

    Column = maxx(filter('Table','Table'[ISIN]=EARLIER('Table'[ISIN]) && not(ISBLANK('Table'[ChangeWeek])) && 'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])

Column 2 = if(ISBLANK('Table'[ChangeWeek]),BLANK(),maxx(FILTER('Table','Table'[ISIN]=EARLIER('Table'[ISIN]) && ('Table'[Date])=EARLIER('Table'[Column])),('Table'[ChangeWeek])))

     https://www.dropbox.com/s/kexca62xapy8j41/Filter2.pbix?dl=0

amitchandak's avatar
amitchandak
Super User
6 years ago

hwoehler , can ypu let us know the logic of last column. Not very clear from the example

hwoehler's avatar
hwoehler
Helper I
6 years ago

Hi amitchandak ,

you're right. The screenshot is misleading. This screenshot should be better:
desired column "new"The column "new" consists of (always) the previous value. (based on column "ChangeWeek".
Best regards, hwoehler

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try , as new columns

    Column = maxx(filter('Table','Table'[ISIN]=EARLIER('Table'[ISIN]) && not(ISBLANK('Table'[ChangeWeek])) && 'Table'[Date]<EARLIER('Table'[Date])),'Table'[Date])

Column 2 = if(ISBLANK('Table'[ChangeWeek]),BLANK(),maxx(FILTER('Table','Table'[ISIN]=EARLIER('Table'[ISIN]) && ('Table'[Date])=EARLIER('Table'[Column])),('Table'[ChangeWeek])))

     https://www.dropbox.com/s/kexca62xapy8j41/Filter2.pbix?dl=0

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      Yep, EARLIER.