Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Get one week prior text

Hi Experts!

 

I have a table with Date, Code and a calculated column as "Method". Now I need to add another column/measure with previous week's Method. That is a table like this (this is a very small sample):

 

Date   CodeMethod   Prev week Method
27.09.2016   792ASC40-50 Days  35-40 Days
30.10.2016   589FVT35-40 Days  Successful
20.09.2016   269CRE35-40 Days  40-50 Days
23.10.2016   279DEWSuccessful    XXX

 

If relevant, calculated column "Method" is based on another calculated column.

 

I tried several formulas, but nothing worked. 

 

 

 

 

Column=CALCULATE(AVERAGE(Table[Method),FILTER(ALL(Table),Table[Date]=TODAY()-1) 

Measure=CALCULATE(AVERAGE(Table[Method),FILTER(ALL(Table),Table[Date]=TODAY()-1)

 

 

 

 

 

 Please help. Appreciate your help!
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Ch 

    try to add a column like:'

    column = 
MAXX(
    FILTER(
        TableName,
        TableName[Date]=EARLIER(TableName[Date])-7
    ),
    TableName[Method]
)

     

    it worked like:

     

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Anonymous 

    can you fill out the last expected column directly? as your description is not sufficient. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I edited the table a little bit. As an example, In the first row of the table, date is 27.09.2016. So one week back is 7 days back. And then the date should be 20.09.2016. Then "Prev week Method" should be the "Method" of 20.09.2016. Which is 35-40 Days. Hope this helps. Thank you for your time.

       

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        hi Ch 

        try to add a column like:'

        column = 
MAXX(
    FILTER(
        TableName,
        TableName[Date]=EARLIER(TableName[Date])-7
    ),
    TableName[Method]
)

         

        it worked like: