Difference between values from 2 different dates

[zhaoshanhe]

Hi,

a total noob in powerBI and need some help with data modeling.

i need to get the difference between the values corresponding to 2 different set of dates (A - B). B will vary as new data will be uploaded frequently with the latest date and its corresponding value.

For A, the value should be taken from the one with the latest date (i.e. 13/1/2016). The date and value will change.

For B, the value should be taken from the one with 'Start' under the Remarks (i.e. 17/4/2013). It will always be this date and value.

can anyone please help me with this? thanks alot

https://imgur.com/a/5ygEH

[OwenAuger]

Hi @zhaoshanhe

With your table set up as it is, a measure like this will perform this calculation:

Difference =
CALCULATE ( SUM ( YourTable[Qty] ), LASTDATE ( YourTable[Date] ) )
    - CALCULATE ( SUM ( YourTable[Qty] ), YourTable[Remarks] = "Start" )

As a side note, I would recommend creating a Calendar table. Among other things, you could flag the Start date just once in the Calendar table (assuming the Start date is the same for all Products).

[zhaoshanhe]

Good Day Sir.

Thank you for taking a look at my problem. I tried your suggestion but there is an error "A function 'LASTDATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

i may have oversimplified the problem and have revised the sample table to make the issue more reflective of the problem i am trying to solve. The main points are as follows:

- I need to get the Qty difference for each product (A to D) between the latest date and the date with 'Start' remarks

- The start date and value for each individual product varies

Or it is more feasbile to create separate Tables for each Product from A to D?

Screenshot of my problem

LINK