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
mtrevisiol
Helper IV
Helper IV

Calculate incremental quantity month by month

Hi everyone.

I've got this table which shows quantities entering and exiting the warehouse, month by month.

In the first row I have the initial stock of the first day of 2021 and I want to create the column "Running Qty" that calculates the resulting stock after entries and exits:

 

ItemYearMonthDataInitial stockQtyRunningQty
000BAGG000A122021101/01/20212240-20 
000BAGG000A122021201/02/20210-20 
000BAGG000A122021301/03/20210-70 
000BAGG000A122021401/04/20210140 
000BAGG000A122021501/05/20210-170 
000BAGG000A122021601/06/20210-90 
000BAGG000A122021701/07/20210-35 
000BAGG000A122021801/08/202100 
000BAGG000A122021901/09/2021065 
000BAGG000A1220211001/10/20210-70 
000BAGG000A1220211101/11/20210-135 
000BAGG000A1220211201/12/20210-45 
000BAGG000A122022101/01/20220-20 
000BAGG000A122022201/02/20220-135 
000BAGG000A122022301/03/20220-105 
000BAGG000A122022401/04/20220-225 
000BAGG000A122022501/05/20220-115 
000BAGG000A122022601/06/20220135 
000BAGG000A122022701/07/20220-125 
000BAGG000A122022801/08/202200 
000BAGG000A122022901/09/202200 
000BAGG000A1220221001/10/202200 
000BAGG000A1220221101/11/202200 
000BAGG000A1220221201/12/202200 

 

The ideal result would be:

mtrevisiol_0-1659533148236.png

I need to make a formula like:

IF (YEAR = 2021 & MONTH = 1, RunningQty = Initial Stock + Qty) 

ELSE Running Qty = Running Qty of the previous month + Qty of current month.

 

Here is the pbix file: https://www.dropbox.com/s/z6cwp0swofjwk1q/RunningQty.pbix?dl=0

 

Thank you for the help!

 

2 REPLIES 2
amitchandak
Super User
Super User

@mtrevisiol , A new measure

 

Sum(Table[Initial Stock]) + Sumx(filter(all(Table), [Item] = Max(Table[Item]) && [Date] <= Max(Table[Date]) ), Table[Qty] )

 

 

or

new column =
Table[Initial Stock] + Sumx(filter((Table), [Item] = earlier(Table[Item]) && [Date] <= earlier(Table[Date]) ), Table[Qty] )

I created the new column using your formula, but I didn't get what I needed..

 

mtrevisiol_0-1659538618215.png

 

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.