Measures - Last Price across different location

[PBI_User2300]

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

[v-piga-msft · Accepted solution]

Hi @PBI_User2300,

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

LPRCA = 

VAR adate =

    CALCULATE ( MAX ( 'Table'[Order Date]), 'Table'[Location] = "A")

RETURN

    CALCULATE (

        MAX ( 'Table'[Price] ),

        FILTER (

             'Table' ,

            'Table'[Location] = "A"

                && 'Table'[Order Date] = adate

        )

    )

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.

Hope it can help you!

Best Regards，

Cherry

[PBI_User2300]

Hi @v-piga-msft

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

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[PBI_User2300]

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.

Appreciate your help.

[Ashish_Mathur]

Hi,

Refer to the revised file here.

Hope this helps.

[PBI_User2300]

Hi @Ashish_Mathur

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

Thanks again

[Ashish_Mathur]

Hi,

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

[PBI_User2300]

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) 

Thanks for your help.

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[PBI_User2300]

Thanks v-piga-msft. It worked.

Appreciate your help.