Forum Discussion

Akos07's avatar
Akos07
Helper I
4 years ago
Solved

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...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Akos07
Helper I
4 years ago

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