cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mdhenzel
Frequent Visitor

Running Total Measure that goes by row and column in Matrix if possible..

Hi All, This is something that is probably not possible, but I would like to ask first before giving up. I am building a running inventory visual for an area of a report. I used a measure that has worked before for me, but that was for looking at a material overall across time. Now, doing it by customer and material throws it off. Is it possible to have this measure work top to bottom or something? Example: June 29th top value of 19 (not -1), then the lower would still take the 3 off for the total of 16. I have it down to the day to solve issue, but data will be looked at as month to month.  

 

mdhenzel_1-1656514342156.png

 

 

 

 

3 REPLIES 3
amitchandak
Super User
Super User

@mdhenzel , Not very clear. But you use a date table and use that in visual too

 

then this will only give cumulative across date for material/customer combination

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

 

 

or allselected

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

if use the table(sales in above example) in place of the date table, then you need to add additional conditions

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hi there, I currently am using the following measure:

 
Inv. Running =
Var First = [Unrestricted to Use]

VAR LastVisibleDate =
MAX ( 'Date Table'[Date] )
VAR FirstVisibleDate =
MIN ( 'Date Table'[Date] )
VAR LastDateWithSales =
CALCULATE (
MAX ( 'Ready To Ship'[Customer Requirement Date] ),
REMOVEFILTERS ()
)
VAR Result =
First +
IF (
FirstVisibleDate <= LastDateWithSales,
CALCULATE (
[Orders to Ship] ,
'Date Table'[Date] <= LastVisibleDate
)
)
RETURN
Result
 
This is only getting me the correct values at the total level for the columns. Not through the values, as shown in my images. 
I do have a date table and it is being used as the columns in the matrix. 

 

mdhenzel
Frequent Visitor

To Add, for July, it would be 16 - 15 for 11, - 25 for -14 and so on. If not possible, do we know why the measure is working in this way?

 

mdhenzel_0-1656515415632.png

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors