Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Price variation

Hello!

I need to create for my colleagues a table with the different prices that a product has had (for example, if in May I have sold it 20 times at the same price I only want to see a price), and the variations that this new price has with respect to the previous one, which I am only getting when I do not filter or sort by date.

I created this matrix

The variation measure is calculated with price measures that are a lookupvalue of the price value for the following date measurements

Last invoice date = MAXx(filter(VALUE_ENTRY,VALUE_ENTRY[Unit price]<>0),VALUE_ENTRY[Index])
Penultimate invoice date = MAXx(filter(VALUE_ENTRY,and(VALUE_ENTRY[Unit price]<>0,VALUE_ENTRY[Index]<MAXx(filter(VALUE_ENTRY,VALUE_ENTRY[Unit price]<>0),VALUE_ENTRY[Index]))),VALUE_ENTRY[Index])
The penultimate invoice date does not return me value in which the graph is affected by dates, I tried this
Closes penultimate invoice ok = calculate([Penultimate invoice],allexcept(VALUE_ENTRY,'ITEM'[No]))
But then he gives me back the penultimate of the whole period, let's not say October 2021; If here again I filter it does not return value.
Can you help me? Thank you!

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Syndicate_Admin
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    usually we can create a new column

     

    new column =

    var _max = maxx(filter(Table, [Item] = earlier([Item])  && [Date] < earlier([Date]) ), [Date])

    return

    maxx(filter(Table, [Item] = earlier([Item])  && [Date] =_max), [price])

     

    Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8