Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Bring Last period

Hello Everyone!

 

I need to bring the last period value (Price) for a especific material, for example I bought a specfic material in the week 9 for 94.25 USD, the week 11 for 94.00, the week 10 I didnt buy the product but i want to compare to the last period i realized a purchase.

I am using this calculated column, but it bring the -7 days value, and I need last period:  

last week price=
CALCULATE (
    MAX ( 'Datos compras'[Price] );
    FILTER (
        'Datos compras';
        EARLIER ( 'Datos compras'[date] )
            = DATEADD ( 'Datos compras'[date]; +7; DAY )
            && 'Datos compras'[MATERIAL] = EARLIER ( 'Datos compras'[MATERIAL] )
    )
)

 

My data looks like this: 

I would like to see the last value, regarthless of the date

I need some help please!

 

Thanks

Need Help
Show and Tell
Tutorial Requests
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may add an index column with RANKX Function and then get the last period price with index column.For example:

    Index =
RANKX (
    FILTER ( 'Table', 'Table'[Material] = EARLIER ( 'Table'[Material] ) ),
    'Table'[Date],
    ,
    ASC
)

    Last Period Price =
CALCULATE (
    MAX ( 'Table'[Price] ),
    FILTER (
        'Table',
        'Table'[Material] = EARLIER ( 'Table'[Material] )
            && 'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
    )
)

    Regards,

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous 

    You may add an index column with RANKX Function and then get the last period price with index column.For example:

    Index =
RANKX (
    FILTER ( 'Table', 'Table'[Material] = EARLIER ( 'Table'[Material] ) ),
    'Table'[Date],
    ,
    ASC
)

    Last Period Price =
CALCULATE (
    MAX ( 'Table'[Price] ),
    FILTER (
        'Table',
        'Table'[Material] = EARLIER ( 'Table'[Material] )
            && 'Table'[Index]
                = EARLIER ( 'Table'[Index] ) - 1
    )
)

    Regards,