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
STS_Joshua
Helper II
Helper II

Running Total

Hi All,

 

I have an issue I could use some help with. I need to create a running total measure instead of the below.

example.JPG

 

Where that has +'s and -'s for each month, this data isn't really totaled up so that it makes sense looking in the future.

Where I have this:

 

Current Month:

Current Inventory: 10

Ins: 5

Outs: 6

Final Inventory : 9

 

Next Month:

Current Inventory: 0 

Ins: 0

Outs: 7

Final Inventory : -7

 

I'd like this:

 

Current Month:

Current Inventory: 10

Ins: 5

Outs: 6

Final Inventory : 9

 

Next Month:

Current Inventory: 9 

Ins: 0

Outs: 7

Final Inventory : 2

 

Thanks!

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Try Like this . Adding Inventory from day one.  I have used the same table. Use appropriate tables 

 

 

Inventory till date = 
Var _min_date_for_inventory = CALCULATE(maxx(all('Date'[Date Filer]),'Date'[Date Filer]))
Var _Todays_date=CALCULATE(maxx(ALLSELECTED('Date'[Date Filer]),'Date'[Date Filer]))

Var _inventory= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date]<=_min_date_for_inventory)
Var   Sale_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))
Var   purchase_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))

return
_inventory+Sale_till_tody-purchase_till_tody

 

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

Try Like this . Adding Inventory from day one.  I have used the same table. Use appropriate tables 

 

 

Inventory till date = 
Var _min_date_for_inventory = CALCULATE(maxx(all('Date'[Date Filer]),'Date'[Date Filer]))
Var _Todays_date=CALCULATE(maxx(ALLSELECTED('Date'[Date Filer]),'Date'[Date Filer]))

Var _inventory= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date]<=_min_date_for_inventory)
Var   Sale_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))
Var   purchase_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))

return
_inventory+Sale_till_tody-purchase_till_tody

 

 

Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.