Price calculations help

[Akos07]

Hello,

I run into a problem, and i cant find a good solution. I could use some advice 🙂

My problem:

I have a price table that contains gross prices, productid, start date, end date(nullable) . Its a historic table.

ID  productid       start            end        gross
1    1                2022.06.01 2022.06.04    100 

2    1                2022.06.05       null          200 

3    1                2022.06.07       null          240  

4    1                2022.06.07       null          300 

i have a date slicer and i need to know the current price based on that slicer. (I have a dedicated measure table, for measures.)

2022.06.02 --> 100

2022.06.05 --> 200 

2022.06.07 --> 300 (if it start at the same time table id decide the price)

The SQL:

SELECT ProductId, Net, Gross FROM (SELECT ProductId, Net, Gross, ROW_NUMBER() OVER(PARTITION BY PriceItems.ProductId ORDER BY PriceHeaders.PriceHeaderId DESC) nr FROM PriceItems INNER JOIN PriceHeaderRows ON PriceHeaderRows.PriceHeaderRowId = PriceItems.PriceHeaderRowId INNER JOIN PriceHeaders ON PriceHeaders.PriceHeaderId = PriceHeaderRows.PriceHeaderId WHERE PriceHeaders.Status = 0 AND PriceHeaderRows.PriceRowId = @PriceRowId AND PriceHeaders.Start <= @Date AND (PriceHeaders.[End] IS NULL OR PriceHeaders.[End] > @Date)) AS Prices WHERE Prices.nr = 1

[v-eqin-msft · Accepted solution]

Hi @Akos07 ,

Please try:

Measure = 
var _id= CALCULATE(MAX('Table'[ID]),FILTER('Table',[start]<=MAX('Dates'[Date]) && ( [end]=BLANK() || [end]>=MAX('Dates'[Date])))) 
return MAXX(FILTER('Table',[ID]=_id),[Gross])

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Akos07]

Thanks a lot @v-eqin-msft 

It was a great help 🙂

[v-eqin-msft]

Hi @Akos07 ,

Oh sorry for that. 

So if the logic is ——

1.ID is not filtered , use the max gross

2.Select a specific ID, then the specific gross

3.Select multiple IDs, then the sum of gross?

Best Regards,
Eyelyn Qin

[Akos07]

ID has no slicer. If you have more records with the same start the hightest ID decide.

1    06.03 112

2    06.05 - 06.07 115
3    06.09 100
4    06.09 300
5    06.09 125

So at 06.03 the price is 112, this is the only record.
06.04 also 112, no changes ID 1 is still valid.
06.05 the price changes to 115. Higher ID.

06.06 same 115

06.07 same 115

06.08 ID 2 is not valid, so the price returns to 112.
06.09 you have 3 records with the same start, but ID 5 is the last record so, the correct price is 125.

(PS: I know its "fantastic" logic 🙂 )

[v-eqin-msft]

Hi @Akos07 ,

Please try:

Measure = CALCULATE(SUM('Table'[gross]),FILTER('Table',[start]<=MAX('Dates'[Date]) && ( [end]=BLANK() || [end]>=MAX('Dates'[Date])))) 

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Akos07]

Hey,

Thanks for the reply. I tried someting similar, but the problem is the same. As you can see 06.07 the gross prices adding up 740, it should remain 300.