Price development calculation

[Anonymous]

Dear all,

I've unsuccesfully tried a couple of days to come up with a DAX formula to calculate the price development per item, but came to a stage now to ask the PowerBI community for help. 

Let me try to briefly explain what i do like to calculate.

I have the following two tables:

- fPriceMutations, which consist of:

Date          Item   Price
1-1-2017     A     1
1-1-2017     B     1
1-1-2017     C     1
1-2-2017     A     0,5
1-2-2017     B     0,5
1-2-2017     C     0,5
1-2-2017     D     2

Important note is that this table logs the initial price at first, and thereafter just the mutation of the last PTD price (price until previous month), considering the initial price and the mutations so far. So in this case, for item A the price of Februari 2017 is 1,5. 

- dDates

Consists of just a regular Dates dimension table with columns like Date, month, month nr, financial year and so on. Current date frame is from 2009-2029. 

Only relationship in the model is from 'fPriceMutations'[Date] > 'dDates'[Date].

Now i want to create a DAX formula that enables me to see the following in a PivotTable:

See all years and months (2009/2019), and show the PTD (initial + mutations) price per item overtime. Of course and also in this case, item A was initially created in Januari 2017, but for all the previous years i would like to see the initial price and from that moment on the PTD price until 2029. 

Hope this is enough information as i didn't manage to include a pbix/excel file. 

Looking forward to hear about any possible solutions!

Thanks in advance,

Ruben

[sturlaws]

Hi, @Anonymous,

here is an example using pbi desktop matrix visual, example, using this measure

Measure =
CALCULATE (
    SUM ( 'Table'[Price] );
    FILTER ( ALL ( vDate[Month] ); vDate[Month] <= SELECTEDVALUE ( vDate[Month] ) )
)

and dates and items on rows and columns, respectively.

[Anonymous]

Hi, @sturlaws,

First of all thanks for your quick reply!

Unfortunately this is not exactly what i meant.

Below i will add a picture of the desired table view. So, even though there are only prices in January and February 2017, i would like to always see the initial price (price at item introduction) for the full calendar before that actual month, and after that the initial price including all the mutations per month. 

Hope this makes it more clear in what i would like to have.

Looking forward to hear from you, or anyone else in their solution!

Thanks in advance,

Ruben

[sturlaws]

Try this code

Measure =
VAR _minDate =
    CALCULATE ( MIN ( 'Table'[Date] ); ALL ( vDate ) )
VAR _firstPrice =
    CALCULATE (
        SUM ( 'Table'[Price] );
        FILTER ( ALL ( vDate ); vDate[Date] = _minDate )
    )
RETURN
    IF (
        SELECTEDVALUE ( vDate[Month] ) < _minDate;
        _firstPrice;
        CALCULATE (
            SUM ( 'Table'[Price] );
            FILTER ( ALL ( vDate[Month] ); vDate[Month] <= SELECTEDVALUE ( vDate[Month] ) )
        )
    )

[Anonymous]

Hi @sturlaws,

Thanks! This is very close to what my intention was, although i am a bit struggling with the following:

- why isn't it possible to have a table showing the price development in year/month instead of the date, this makes it somewhat nicer. Now it does the following (after having added two columns in the vDate table, being Year and Month nr.

- Also, could you help me with the following? Now we have the prices for each month i would like to compute these against the number of items delivered. But this should be YTD (financial year is from 1-4 till 31-3). So, i would like to calculate the total costs by multiplying the item prices against the number of items delivered YTD. So prices in April until March should each be computed (multiplied) by the number of items delivered during that timeframe. That enables me to see the total efficiency results within the FY, as is leaves all the price mutations at the bottom, due to the fact that when new items are introduced this does not effect the total costs because the formule that you've created handles the item to have always existed. 

I hope you would still want to help me.

Thanks in advance,

Ruben

[sturlaws]

for your first question, there were a couple of glitches in the dax-code. Try this instead:

Measure =
VAR _minDate =
    CALCULATE ( MIN ( 'Table'[Date] ); ALL ( vDate ) )
VAR _firstPrice =
    CALCULATE (
        SUM ( 'Table'[Price] );
        FILTER ( ALL ( vDate ); vDate[Date] = _minDate )
    )
RETURN
    IF (
        MIN ( vDate[Month] ) < _minDate;
        _firstPrice;
        CALCULATE (
            SUM ( 'Table'[Price] );
            FILTER ( ALL ( vDate ); vDate[Month] <= MIN ( vDate[Month] ) )
        )
    )

For your second question, it's doable, but might get a bit tricky. What you are doing now is creating sort of a flat table in query time. Life will be much easier for you if you flatten the table when you read it in. But it will depend on the amount of data. How many items/products do you have?

[Anonymous]

This code works even better, thanks a lot @sturlaws.

As you already answered my initital question for which this post was made, would you like me to accept it as the solution already?

With regards to my second question, i have about 300k items. So therefore, instead of loading the prices of all items each month i'll just load the initital price (once entered/introduced) and thereafter just when an item has a mutation on its price. Roughly this means about 20k rows a month (both new items and mutations). 

In addition to this table there's a QuantityDelivered fact table, which consists of the number of items delivered each month. What i would like to do is to compute the total number of items delivered (per item of course) within a financial year (01-04 - 31-03) and multiply it by the price per month. This will in the bottom result in the total efficiency (per FY and month), being the items becoming cheapier/more expensive. New items will not have an impact on this efficiency result, as by the previous calculation the price development of this new item will be filled historically as well. 

Herewith an example:

Also find this pbix file which contains a more or less same example (just different data).

Price development calculation.pbix

Hope you'll be able to help me on this as well!

Thanks in advance,

Ruben

[sturlaws]

the link to the sample does not work

[Anonymous]

Excuse me, i was not really familiar with how to publish a document. But this one should work:

Price calculation pbix

Looking forward hearing from you!

Thanks in advance,

Ruben