cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Akos07
Helper I
Helper I

Price calculations help

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

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

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])

Eyelyn9_0-1657074099146.pngEyelyn9_1-1657074116659.png

 

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.

View solution in original post

6 REPLIES 6
Eyelyn9
Community Support
Community Support

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])

Eyelyn9_0-1657074099146.pngEyelyn9_1-1657074116659.png

 

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.

Thanks a lot @Eyelyn9 

It was a great help 🙂

Eyelyn9
Community Support
Community Support

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

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 🙂 )

Eyelyn9
Community Support
Community Support

Hi @Akos07 ,

 

Please try:

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

Output:

Eyelyn9_0-1656905548447.png

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.

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. 

 

Akos07_0-1656914440869.png

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors