cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measures - Last Price across different location

Hello Eveyone

I am trying to list the last price sold on item across different location in a table visual , and calculating the difference. But i am struggling to acheive this.

I am using the below measures to calculate the difference.

LPRCA = calculate(MAX('table1'[Price]),LASTDATE('table1'[Order Date]),'table1'[Location] = "A")

LPRCB = calculate(MAX('table1'[Price]),LASTDATE('table1'[Order Date]),'table1'[Location] = "B")

Help ! Help !

Thanks

1 ACCEPTED SOLUTION
Resident Rockstar

You could create the measure below to get the last price sold on item across different location and the difference.

```LPRCA =
CALCULATE ( MAX ( 'Table'[Order Date]), 'Table'[Location] = "A")
RETURN
CALCULATE (
MAX ( 'Table'[Price] ),
FILTER (
'Table' ,
'Table'[Location] = "A"
)
)```
```LPRCB =
VAR bdate =
CALCULATE ( MAX ( 'Table'[Order Date] ), 'Table'[Location] = "B" )
RETURN
CALCULATE (
MAX ( 'Table'[Price] ),
FILTER (
'Table' ,
'Table'[Location] = "B"
&& 'Table'[Order Date] = bdate
)
)```

The picture of the result is below.

Best Regards，

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Resident Rockstar

You could create the measure below to get the last price sold on item across different location and the difference.

```LPRCA =
CALCULATE ( MAX ( 'Table'[Order Date]), 'Table'[Location] = "A")
RETURN
CALCULATE (
MAX ( 'Table'[Price] ),
FILTER (
'Table' ,
'Table'[Location] = "A"
)
)```
```LPRCB =
VAR bdate =
CALCULATE ( MAX ( 'Table'[Order Date] ), 'Table'[Location] = "B" )
RETURN
CALCULATE (
MAX ( 'Table'[Price] ),
FILTER (
'Table' ,
'Table'[Location] = "B"
&& 'Table'[Order Date] = bdate
)
)```

The picture of the result is below.

Best Regards，

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Grand Total for these measures are not correct (I noticed it today). Any idea how to resolve those ? I read lot of articles end up confused. Help !

Thanks

Super User

Hi,

You may refer to my solution here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Thanks @Ashish_Mathur

I tried your solution and received this error when there is more than one Part Number

( I added 1000 as new part number )

My bad, i should have added few Part numbers in my sample table.

Super User

Hi,

Refer to the revised file here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

i am  looking to resolve the Grand Total ( already resolved Last Price ) .. any suggestions ?

Thanks again

Super User

Hi,

What answer are you expecting in the Grand Total row?  Please tell me the exact numbers that you want there.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

I want to see A=32 (19+13) and B=21 (11+10) in "Totals" row. I read lot of posts related to this, and it looks like a complex one to resolve, why dont it just add the data in each row displayed and show it in "Total" row :). I am sure there is a logic of how its calculated currently (showing 13 and 10 Respectively)

Super User

Hi,

You may refer to my solution here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Thanks v-piga-msft. It worked.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.