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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
tkavitha911
Helper I
Helper I

This is the input ,I want to calculate the closing stock for every month using March closing stock.

formula = previous month closing stock +receipts+production-demnad 

BrandProduct CodeDateSum of Closing StockDemandProductionReceipts
Brand A69419301-03-202235255   
Brand A71650401-03-202226703   
Brand A72275701-03-202219308   
Brand B72822101-03-202229024   
Brand B73668201-03-202230673   
Brand C74879801-03-202239212   
Brand C74906401-03-20223240   
Brand A69419301-04-2022 30535021780
Brand A71650401-04-2022 22203.25151730
Brand A72275701-04-2022 16017.29204090
Brand B72822101-04-2022 24531176350
Brand B73668201-04-2022 26039.67202780
Brand C74879801-04-2022 39211.58376220
Brand C74906401-04-2022 1078.83401045.334
Brand A69419301-05-2022 24891020100
Brand A71650401-05-2022 1830421621.380
Brand A72275701-05-2022 16072.5414351.330
Brand B72822101-05-2022 22127.6727013.330
Brand B73668201-05-2022 24910.6724362.170
Brand C74879801-05-2022 37621.4242036.250
Brand C74906401-05-2022 1082.83401156.167
Brand A69419301-06-2022 23271012580.93
Brand A71650401-06-2022 19663.4612442.50
Brand A72275701-06-2022 17261.9614012.170
Brand B72822101-06-2022 27013.8310103.330
Brand B73668201-06-2022 24362.6720547.830
Brand C74879801-06-2022 42037.0821671.130
Brand C74906401-06-2022 1074.6670993.501
Brand A69419301-07-2022 12225023230.1
Brand A71650401-07-2022 13363.7910917.580
Brand A72275701-07-2022 17061.468524.8330
Brand B72822101-07-2022 10103.33202760
Brand B73668201-07-2022 20547.8313400.170
Brand C74879801-07-2022 21671.1322687.370
Brand C74906401-07-2022 00334.833
1 ACCEPTED SOLUTION
xifeng_L
Super User
Super User

Hi @tkavitha911 

 

You can try below calculated column expression.

 

xifeng_L_0-1718461309540.png

Closing Stock = 
CALCULATE(
    SUMX('Table','Table'[Sum of Closing Stock]+'Table'[Production]+'Table'[Receipts]-'Table'[Demand]),
    ALLEXCEPT('Table','Table'[Brand],'Table'[Product Code]),
    'Table'[Date]<=EARLIER('Table'[Date])
)

 

Demo - This is the input ,I want to calculate the closing stock for every month using March closing ...

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

View solution in original post

3 REPLIES 3
tkavitha911
Helper I
Helper I

Thank you for your assistance, your solution resolved my issue, and I have marked your post as the solution and added Kudos.

xifeng_L
Super User
Super User

Hi @tkavitha911 

 

You can try below calculated column expression.

 

xifeng_L_0-1718461309540.png

Closing Stock = 
CALCULATE(
    SUMX('Table','Table'[Sum of Closing Stock]+'Table'[Production]+'Table'[Receipts]-'Table'[Demand]),
    ALLEXCEPT('Table','Table'[Brand],'Table'[Product Code]),
    'Table'[Date]<=EARLIER('Table'[Date])
)

 

Demo - This is the input ,I want to calculate the closing stock for every month using March closing ...

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

tkavitha911
Helper I
Helper I

I need assistance with calculating the closing stock for each month using DAX, starting from the March closing stock. Could someone please help me with this.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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