Add values from sum of another table starting from the most recent date

[fluffyhead]

Hello All, 

Being new to Power BI, I'm unsure how to go about finding this solution. I've included a screenshot of what I've done in Excel, but would like to do in Power BI. 

Below is a Pivot of a table sorted by Posting Date and Amount in LC. The column Inventory Movement was added by me. What I've done to populate Inventory Movement is take the SUM of a column titled "Value Unrestricted" and added the amount starting from the most recent date. $253k + $8.5k = $261k + -$1.2k = $260k and so on as shown below. Going forward, I just want to capture the most recent date and add Amount in LC to the sum of Value Unrestricted in descending dates.

Thanks in advance for the help!

[v-shex-msft · Accepted solution]

Hi @fluffyhead,

You can try to use following measure to get previous amount to calculate value unrestricted.

Value Unrestricted =
VAR _current =
 SELECTEDVALUE ( table[Inventory Movement] )
VAR _currentDate =
 MAX ( table[post date] )
VAR _previous =
 MAXX ( FILTER ( ALL ( table ), [post date] < _currentDate ), [post date] )
RETURN
 _current
 + CALCULATE ( SUM ( table[Amount in LC] ), table[post date] = _previous )

Regards,
Xiaoxin Sheng