Calculate total price with unit price changing over time

[AnthonyXelya]

Hi everyone,

I have a table with some sales quantity of a product in the form of a subscription, I mean, I have the beginning and the end of the subscription, the quantity, and every month in this period, the quantity is sold to the customer.

In another table, I have the prices of the product, with the start of each price. 

So the cost for the customer can change from one month to the other. 

How can I calculate how much the customer has to pay each month?
What I would like to have is :
- Customer A will have to pay 25 for product X in January, 50 in february, march, april and may
- Customer A will have to pay 50 for product Y in January and february, 60 for march, april and may

- Customer B will have to pay 15 for product X in january, 30 in february, march, april and may...

In fact, I don't know how to get the acurate price for the right month... 

[johnt75]

You'll need the prices table to have a unique identifier for each row, you can add an index column using power query. select the prices table in the modelling view and set the key column to be the index column, that tells power bi that there are no duplicates, and you can create a measure like

Total Cost = SUMX( 'Subscriptions',
	VAR CurrentProduct = 'Subscriptions'[Product]
	RETURN SUMX( KEEPFILTERS( VALUES('Date'[Year Month])),
		VAR ReferenceDate = MAX( 'Date'[Date])
		VAR PriceToUse = SELECTCOLUMNS(
			INDEX( 1,
				FILTER( 'Prices',
					'Prices'[Product] = CurrentProduct && 'Prices'[PriceStart] <= ReferenceDate
				),
				ORDERBY( 'Prices'[PriceStart], DESC)
			),
			"@price", 'Prices'[Price]
		)
		RETURN PriceToUse * 'Subscriptions'[Qty]
	)
)

[AnthonyXelya]

Thank you for your answer.

My price table already has a unique identifier (let's call it "PriceId"), maybe that's why the line 

"@price", 'Prices'[Price]

doesn't work 😕

I don't really get what I'm supposed to put into the 

VALUES('Date'[Year Month])

 since my calendar table only has a column "Date".

[johnt75]

The prices line should be fine, that's just choosing the price column from the row returned by index. 

You'll need a year month column in your date table as you will need to use that to display how much a customer owes in a given month, and the calculation needs to know what dates it covers so that it can work out the correct price. You can add a column to your date table like

Year month = DATE( YEAR('Date'[Date]), MONTH( 'Date'[Date]), 1)

and choose an appropriate date format from the dropdown.

[AnthonyXelya]

I had to add a MAX in the price line, because it need a scalar value.

But it still tells me that the Relation parameter of INDEX may have duplicate rows... So it doesn't work 😞

[johnt75]

Have you marked your priceID column as the key column for the table ?

[AnthonyXelya]

That was the problem, but now that the measure is OK, the value is wrong (maybe because of the MAX I added).

It only shows me the max price no matter the month I select 😕 But if I don't put the MAX, the measure waits for a scalar value 😞

[johnt75]

There must be something else going on, because that code worked for me. When there is only 1 row, as returned by INDEX, then SELECTCOLUMNS returns a scalar value.

[AnthonyXelya]

The intellisense only proposes me columns from "Subscriptions" (the table you call in your SUMX), like if the Price table is out of scope at this step.

[johnt75]

I think that's just Intellisense. The Prices table is in scope because the SELECTCOLUMNS, INDEX and FILTER are all operating on it.

Is it possible to share a PBIX with any confidential info removed?

[AnthonyXelya]

My PBIX is quite heavy 😕
Here is the error (it's in french).

[johnt75]

Yes, that's just Intellisense. If there was an actual error there would be a yellow bar with an error message at the bottom.

You have an error in the formula though. In the FILTER it shouldn't be ServiceDetail[ServiceID], it should be ServicePrice[ServiceID].

[AnthonyXelya]

It seems to work!!

Thank you!

One last question... If I want to cumulate each month for the whole year (in january, I would only have january, in february I would have january + february, etc...), including the potential price changings?

[johnt75]

You could try

Cumulative cost =
SUMX (
    CALCULATETABLE ( VALUES ( 'Date'[Year month] ), DATESYTD ( 'Date'[Date] ) ),
    [Total Cost]
)

[AnthonyXelya]

It doesn't work (maybe because there is no link between my Date table and my other tables, I'm filtering the measures using conditions in FILTERs).

In january, it seems correct, but it stops working in february 😞