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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dhannaa
Helper IV
Helper IV

Cumulative sum

Hi all,

 

I am trying to achiece a stock report that gives stock value per different products for any given date. 

 

To achieve this - and other cool things as well - I would like to have a new column for each cumulative sum (per given item and date).

 

qty.PNG

 

I have tried many ways and my code just doesn't seem to work... I am new to Power BI and certainly not a coder 🙂

1 ACCEPTED SOLUTION

@dhannaa,

 

Hi Jenny,

 

I guess you have a date table in this scenario. Let's call it "Calendar". Then you can try this formula. 

AccumulatedValue =
CALCULATE (
    SUM ( Table1[Qty] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MIN ( 'Calendar'[Date] ) )
)

Add a date slicer from "Calendar".Cumulative sum2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

20 REPLIES 20
v-jiascu-msft
Employee
Employee

Hi @dhannaa,

 

Just see from your data, you could try this formula. I added some more data to make it clear.

 

Cumulative sum =
CALCULATE ( SUM ( Table1[Qty] ), ALL ( Table1[Qty] ) )

Cumulative sum.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi again @v-jiascu-msft,

 

I tested your suggestion further and after all it didn't work the way I wanted. Sorry if I was being unclear at first. Below is table of what I need:

 

Sieppaa.PNG

 

Every row represents one stock movement and to get stock for certain date I need cumulative sum from the beginning of time. Now in your example cumulative sum is counted within certain date - this would tell only how stock changes on that day.

Hi @dhannaa,

 

It's easy to modify the formula. I wounder if a calculated column is good enough.

Cumulative sum =
VAR currentDate = 'Table1'[Posting Date]
VAR currentItem = 'Table1'[Item]
RETURN
    CALCULATE (
        SUM ( Table1[Qty] ),
        FILTER (
            'Table1',
            Table1[Posting Date] <= currentDate
                && 'Table1'[Item] = currentItem
        )
    )

 

Cumulative sum2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 


(I edited my post a little as I thought my case through one more time)

 

------

Again thank you very much for your response @v-jiascu-msft! As said earlier I am new to PowerBI and not a coder at all. But I am eager to learn it and driving our organization towards using it. So getting your help is highly important to me, appreciate it a lot!

 

Your solution seems to work fine, but it actually seems my that own plan is not working the way I wanted.

 

Every product does not have stock movement every day - and selecting one date gives me only stock qtys for products with rows on that particular date. Not the whole stock  (other products as well) on that day.  Perfectly logical after all.

 

The counting of calculatied columns itself is pretty slow and if I end up calculating values for every single date it propably ain't wise at all.

 

Could my goal be achieved with DAX? With a formula that gives cumulative qty for selected date - and if selected date doesn't exist (in the stock movement data) the formula would look the value of the previous date, if not found then the previous and so on until a value is found.

 

Cheers, Jenny

Is my idea (described above) possible by a any means? Or does it have some error in logic I don't realize?

@dhannaa,

 

Hi Jenny,

 

I guess you have a date table in this scenario. Let's call it "Calendar". Then you can try this formula. 

AccumulatedValue =
CALCULATE (
    SUM ( Table1[Qty] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MIN ( 'Calendar'[Date] ) )
)

Add a date slicer from "Calendar".Cumulative sum2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks again @v-jiascu-msft !

 

This solution worked, I kind of figured it out myself through trial and error 🙂

 

 

I've got one more question, is there an easy answer for this? I can't find one though I am trying hard.

 

I am trying to achieve this kind of table that tells stock value for any given month. For now I am only getting stock changes as source data is filtered by BI matrix table (and its dates) itself? Is it possible to bypass that filtering with DAX-formula as I want cumulated data from beginning of time, not beginning of month?

 

 

Sieppaa.PNG

 

Jenny

@dhannaa,

 

Hi Jenny,

 

I found out that changing "min" into "max" would work.

 

AccumulatedValue 2 =
CALCULATE (
    SUM ( Table1[Qty] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

Cumulative sum3.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft

 

Thanks Dale! You rock 😉

 

Jenny

I need to seriously improve my (non existing)  DAX skills as I seem encounter problem every time I want to create a new formula. What would be the way to get the value for the month last year? Tried thousand different approaches with no success. Going to study DAX this weekend :), sounds like a great plan.

 

Jenny

@dhannaa,

 

Hi Jenny,

 

So how is your weekend? In most of the scenarios, the function "Sameperiodlastyear" is enough with a context "Month' in the report. Surely there are still many other ways. 

Reference: https://msdn.microsoft.com/en-us/library/ee634972.aspx

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

My weekend is long gone Smiley LOL , but I did start my learning process on DAX  - it will be some project though  😉

 

With this formula

 

LY AccumulatedValue 2 =
CALCULATE([AccumulatedValue]; SAMEPERIODLASTYEAR('Calendar'[DateKey].[Date]))

 

I get the totally same value for this and last year.. not correct. (I am using matrix table, months in rows, year in columns)

 

(AccumulatedValue  formula is 

CALCULATE (
SUM ( 'Stock entry'[Quantity] );
FILTER ( ALL ( 'Calendar' ); 'Calendar'[DateKey].[Date] <= MAX( 'Stock entry'[Posting Date] )

))  )

 

What goes wrong?

 

It seems that Accumulated value - formula gives sum of qtys for certain range filtered by reports dates no matter what. There is this "take last posting date and sum all the quantities to that moment" -logic in accumulated values -> this seems not to work with SAMEPERIODLASTYEAR as it seems that day filtering is taken from the report and qty values are still summed up according to tables dates, not last year.

 

I have tried many workarounds but keep getting errors and wrong values.

 

BR, Jenny

@dhannaa,

 

Hi Jenny,

 

I think this could be something wrong with the context. Where are the months and year from?Cumulative sum3.jpg

 

 

 

 

 

 

 

 

 

 

BTW, it would be a good idea to open a new thread. Maybe community members want to search solutions. One topic, one solution. Thank you for you understanding.

 

Best Regards!

Dale

 

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft

 

Thanks again!

 

Finally got around my problems mentioned previously. I think there was really an issue with dates.

 

Still got one more problem in daily stock calculations.

(I will definitely open a new thread when this is solved. I think the problem has to do with original question its solution - and therefore I placed my question here.)

 

Accumulated value is currently counted like this:

AccumulatedValue = CALCULATE (
SUM ( 'Stock entries'[Quantity] );
FILTER ( ALL ( 'Calendar' ); 'Calendar'[DateKey].[Date] <= MAX('Stock entries'[Posting Date]))

 

Accumulated value isn't counted in the table below at all if there is no stock movements for that particular date.

(As the trigger for counting is posted line of stock movements and there is none for second day.)


What would be the solution to force counting for this day as well?

 

powerbi.PNG

 

Cheers,

Jenny

 

 

@dhannaa,

 

Hi Jenny,

 

Is the table your visual or source data? There is no date in it. That's why it didn't work. If you have dates, please try to use the dates from table Calendar. A right relationship is also needed.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft

 

Sorry my picture did not tell it all. It was a visual table and there was a calendar table linked to the fact table (dates of my picture/visual were from calendar table), but there must have been something wrong with the calendar table. I anyway managed to tackle all the mysterious problems by remaking my file.  Yes!

 

Jenny

Thank you for your suggestions! I will try them and get back if (and when) I have more challenges 🙂 !

Actually I have a calendar table related to the fact table but I am not sure if it is correctly set up at the moment.

I tried this out and everything worked like a charm. I was being way too complicated with my DAX 🙂

 

Can you furthermore clarify me on what would be the most efficient way to filter / show this stock value per date -data.

 

I have a huge amount of dates and I would like obtain a situation where user selects certain date (one date, not a time period) and gets immediately stock value for that moment in time. 

Hi @dhannaa,

 

I would suggest the custom visual "HierarchySlicer", which is easy to locate the dates.Cumulative sum.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TomMartens
Super User
Super User

Hey,

 

first - create a dedicated calendar table with DAX, by using the CALENDAR(...) function, you may also consider using M within your QueryEditor to build the calendar table, but this almost depends on your personal taste

2nd - create a relationship between your table (m-side) and your calendar table (1-side)

3rd - http://www.daxpatterns.com/time-patterns/

 

hope this gets you started



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.