Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate Stock balance for multiple transactions per date

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

pabsmart_3-1599403569516.png

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

 

 

 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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])


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@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])


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

pabsmart_0-1599436495634.png

 

Thanks for your help again, 

 

Anonymous
Not applicable

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

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

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors