Calculate Stock balance for multiple transactions per date

[Anonymous]

Hi guys, i'm having trouble cracking this one. The formula works fine until i get 2 or more transactions on the same date:

So the balance as at March should be 17 but my formula as above gets to 32. I have tried several variation with LASTNONBLANK or LASTNONBLANKVALUE but couldn't make it work. Thanks, appreciated it. 

Pablo

[Greg_Deckler · Accepted solution]

@Anonymous - You are going to need something to tell Power BI which one is "later". Perhaps you can add an Index to your data and that will resolve the date ties? You could then replace your LASTDATE() with MAX([Index])

[Anonymous]

Thanks @Greg_Deckler  but i get the following error just adding MAX to the calculate statement: 

Thanks for your help again, 

[Anonymous]

// If you have an Index column that
// establishes the order of events...

[Stock By Code] =
calculate(
    sum( Inventario[Actual Quantity] ),
    lastnonblank(
        Inventario[Index],
        1
    )
)

[Greg_Deckler]

@Anonymous - You need more of a filter statement in your CALCULATE versus just a scalar value for your filter. If you can post sample data and expected result that would be best.

[Anonymous]

Thanks a lot @Greg_Deckler . I have solved the calculation with this formula:

Stock by Code =

Var MaxIndex = MAX(Inventario[Index])

RETURN

CALCULATE(SUM(Inventario[Cant. Actual]),LASTDATE(Inventario[Fecha]),FILTER(Inventario,[MaxIndex] = MaxIndex))

Cheers,

Pablo

[amitchandak]

@Anonymous ,

Max(Table[Index]) will not work

you have taken like

Filter(Table, Table[Index] = max(Table[Index]))

or try lastnonblankvalue(Table[index], Sum(Table[Actual Quantity))

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak