Cumulative Total with AddColumns - Stock

[RyanWhite]

Hi everyone,

I am fairly new to DAX so have a feeling I might just be using the wrong DAX for the job as such.

I am attempting to create a measure that would show a predicted stock position - taking away a daily sales average and adding any new stock arriving. Due to the number of SKUs in the final model (200+) I shied away from trying this in calculated columns as I feel it would bloat the model. To aid me create the measure I made a simpler version of the model Test for stock graph.pbix (217.4 KB) with only 3 SKUs.

Inbound Shipments

Monthly Sales

Stock Today

I have attempted to create a virtual table using AddColumns to take the Date from the Date table and then in the second columns “Sales Velocity” - get the daily average sales for each month getting taken away each day and then inbound shipments being added. See DAX below

    ADDCOLUMNS( Values('Dates Table'[Date]),
"Sales Velocity", [Inbound Shipments] - IF('Dates Table'[Date] >= DATE(2021,01,01) && 'Dates Table'[Date] <= DATE(2021,01,31),[January Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,02,01) && 'Dates Table'[Date] <= DATE(2021,02,28),[February Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,03,01) && 'Dates Table'[Date]<= DATE(2021,03,31),[March Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,04,01) && 'Dates Table'[Date] <= DATE(2021,04,30),[April Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,05,01) && 'Dates Table'[Date] <= DATE(2021,05,31),[May Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,06,01) && 'Dates Table'[Date] <= DATE(2021,06,30),[June Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,07,01) && 'Dates Table'[Date] <= DATE(2021,07,31),[July Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,08,01) && 'Dates Table'[Date] <= DATE(2021,08,30),[August Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,09,01) && 'Dates Table'[Date] <= DATE(2021,09,30),[September Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,10,01) && 'Dates Table'[Date] <= DATE(2021,10,31), [October Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,11,01) && 'Dates Table'[Date] <= DATE(2021,11,30),[November Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,12,01) && 'Dates Table'[Date] <= DATE(2021,12,31),[December Sales V],0)))))))))))))

That gives be the subsequent table (when I put it into a table not a measure to visualise what’s going on), this appears to do what I’m after although I feel as though there is probably a more elegant way.

So finally I have tried to apply a cumulative total over the virtual table. The hope was that I could then take the Stock level today - the cumulative total which would produce a graph of a stock level depleting and then going back up when the next inbound shipment arrived.

  Test v2 = 
VAR VirtualTable = 
ADDCOLUMNS( Values('Dates Table'[Date]),
"Sales Velocity", [Inbound Shipments] - IF('Dates Table'[Date] >= DATE(2021,01,01) && 'Dates Table'[Date] <= DATE(2021,01,31),[January Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,02,01) && 'Dates Table'[Date] <= DATE(2021,02,28),[February Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,03,01) && 'Dates Table'[Date]<= DATE(2021,03,31),[March Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,04,01) && 'Dates Table'[Date] <= DATE(2021,04,30),[April Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,05,01) && 'Dates Table'[Date] <= DATE(2021,05,31),[May Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,06,01) && 'Dates Table'[Date] <= DATE(2021,06,30),[June Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,07,01) && 'Dates Table'[Date] <= DATE(2021,07,31),[July Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,08,01) && 'Dates Table'[Date] <= DATE(2021,08,30),[August Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,09,01) && 'Dates Table'[Date] <= DATE(2021,09,30),[September Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,10,01) && 'Dates Table'[Date] <= DATE(2021,10,31), [October Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,11,01) && 'Dates Table'[Date] <= DATE(2021,11,30),[November Sales V],
                  IF('Dates Table'[Date] >= DATE(2021,12,01) && 'Dates Table'[Date] <= DATE(2021,12,31),[December Sales V],0)))))))))))))
VAR X = CALCULATE(VirtualTable,    
            FILTER('Dates Table',
            'Dates Table'[Date] <= MAX('Dates Table'[Date])))

Return X

However, I keep getting errors - “The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.”

Any and all help would be greatly appreciated!

[Greg]

Hi @RyanWhite. I can see from your DAX code that it’s using …[Date].[Date]…, which suggests that you’re using the auto-date/time option and don’t have a dedicated [Dates] table that is marked as such. After disabling the Auto date/time option and verification of the [Dates] table, please attach your work-in-progress PBIX (with sanitized data, if necessary) and a marked-up screenshot or Excel mock-up of the visual for your desired outcome.
Greg

P.S.: Here are a couple of resources that might help:

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

How To Mask Sensitive Data In Power BI

[RyanWhite]

Hi Gregg, thank you for your quick response - not too sure about the auto-date/time option but I followed the video and created a date table and adjusted the formula so there’s no longer .[Date].

I have also uploaded the model to the original post.

[Greg]

Hi @RyanWhite.

Thanks for the PBIX. The [Dates] table is great, but was not marked as such, nor was it related to your data, so there’s still data modelling work to be done. (For example, there is no date in your [Daily Sales] table, just a text value (MMM-YY) that need to be converted to an actual date [I’m guessing you’d be looking for the 15th of the month to be consistent with your [Inbound] table, but please confirm.)

As for disabling auto date/time, here’s a screenshot of the option:

I’m not clear on what your desired outcome is: could you please post a marked-up screenshot or Excel mock-up of the visual you’re looking for (even if the numbers are not correct)?

I’ll have another look once I see your desired outcome.
Greg

[EnterpriseDNA]

Hi @RyanWhite. Welcome to the Forum! It’s great to know that you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[EnterpriseDNA]

Hi @RyanWhite, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @RyanWhite, we’ve noticed that no response has been received from you since the 30th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @RyanWhite, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[RyanWhite]

Hi Greg, apologies for taking so long to respond.

So I have linked the date table to the dates on the Inbound Table. As for the Dates on the on the Daily Sales table, that is the sales for the entire month so the date is supposed to pertain to the entire month of January for example (poorly named I must admit really it should be called monthly sales I will change that - I called it daily sales as I then have measures which divide through by the days in the month to reach a daily sale).

I see what you mean as it is a text it won’t link to the dates table. Therefore, I was looking at changing the data type to date but it change to the first of each month whereas it needs to represent the entire month.

I have disabled the auto date/time - thank you for the image super helpful.

Desired Outcome:
I wrote a macro in excel which effectively generates a table with declining daily sales and adds on the inbound. I’m attempting to replicate this Power BI. Below is an image of the type of graph that is produced and the data that populates the graph

I have highlighted on the table in yellow where the first 2 inbound shipments come in which correspond to the first two peaks in the graph. The sales would be going down by the average daily sales for that month so for example the blue line is decreasing by 100 a day during April, then 5 a day in June, etc (show in a quick table in blue above) this is what I have worked out with a measure by dividing the (newly named) Monthly sales by the days in the month.

I hope that makes sense let me know if there’s any further information that would help. (I think my brain is struggling to stop thinking in terms of VBA)

Thanks
Ryan

[Greg]

Hi @RyanWhite.

I’ve tried to use the data within your sample PBIX, and, while I think the calculations are promising, I can’t get anything remotely like your sample screenshot; I think two quite different datasets are being used.

Here’s the graph I got:

Although your case is stock-related, it’s not dissimilar to purchase and sales transactions, so that’s what I used in my analysis. The steps I took were:

• disabled option for Current File \ Data Load \ Time Intelligence \ Auto date/time
• renamed [Dates Table] to [Dates]
• marked [Dates] table as a date table
• hid [Measures (2)] table
• added [Key Measures] table
• use DAX Studio to extract [Starting Stock] table, then create copy for [Products] and remove stock
• loaded [Products] table
• added table visual, added Products[Product] column
• used DAX Studio to extract [Daily Sales], [Inbound], and [Starting Stock] tables to CSV files, then loaded them as staging queries, disabled “Enable Load” and deleted original data tables
• referenced [Daily Sales] to [Daily Sales 2]; added Type=Sales
• referenced [Inbound] to [Purchase Transactions]; added Type = Purchase
• added [Month-Year] column to [Dates] table

Month-Year = FORMAT( Dates[Date], "mmm-yy" )

• added calculated table for [Sales Transactions] (NATURALINNERJOIN [Dates] and [Daily Sales (2)])
• added [Transactions] table as UNION of [Sales Transactions] and [Purchase Transactions] tables
• added 1-to-many relationship from Dates[Date] to Transactions[Date]
• added 1-to-many relationship from Products[Product] to Transactions[Product]
• added [Key Measures] for [Total Sale Quantity] and [Total Purchase Quantity]
• added [Cumulative Measures] for [Cumulative Sale Quantity], Cumulative Purchase Quantity], and [Balance]

Here’s my work-in-progress PBIX:
eDNA Forum - Stock.pbix (208.0 KB)

If you wish to pursue this further on the forum, I’d suggest you create a simple Excel dataset of the stock increases and stock decreases (i.e., your daily quantity by month), then mockup your desired outcome visual in Excel using the same data.

Greg

[EnterpriseDNA]

Hi @RyanWhite, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @RyanWhite, we’ve noticed that no response has been received from you since the 8th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @RyanWhite, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[RyanWhite]

Hi Greg,

The “Balance” line in your solution is relatively close to my desired outcome and through working through your file it has really helped me get closer. It has given me some inspiration and now I think I’m almost there!

I have changed some of the underlying data to hopefully show the look of graph a bit more clearly.

As my real world data has well over 200 SKUs I have tried to keep everything in virtual tables to not bloat the data set.

Sales Virtual Table (Sales VTable)
I have the amount of sales for the SKU against the Date and then I have a measure creating a cumulative total

Stock Virtual Table (Stock VTable)
I have the starting stock always being added to the virtual table on today. Then I have the inbound quantities being added on their relevant date. Finally, like with the sales I have a measure running a cumulative total.

Then just like how you have, I took the Cumulative Stock away from the Cumulative Sales(CStock Minus CSales). Giving me the following graph:

Now the problem I have is that the stock continues to go into the negative (obviously not possible with stock). So I have put an If function around the measure to make it stop at zero (Stock Line) however this doesn’t fully solve the problem as future inbound quantities should push the stock back into the positive. I have tried to demonstrate how it should look below:

Excel Version using the same data this time (apologies for the confusion before)

If you have any ideas on how to solve this, it’d be greatly appreciated.

Disclaimer: I got to this through a lot of trial and error and believe my relationships to be somewhat messed up
2nd Attempt.pbix (230.5 KB)

[Greg]

Hi @RyanWhite.

A busy few days … just getting to your updated post now. A few things to start:

• you still have the option for “Auto date/time” turned on for your file
• your “Dates Table” table is still not marked as a date table in your file
• I don’t understand your comment about “… I have tried to keep everything in virtual tables …”
  • if your intent was to have calculated tables, then they would contribute directly to the file size
  • as it is, you’ve calculated virtual tables inside 2 measures ([Stock VTable] and [Sales VTable]) both of which return a scalar value, not a table, as measures cannot return a table
• I’m also not sure what the intent is to wrap you calculations with a CALCULATE statement in your [CStock Minus CSales] and [Stock line] measures

As an overall comment, your code looks very complicated, and this should be a simple and quite common problem. I’d suggest you (if you haven’t already) take advantage of the two free Enterprise DNA “Ultimate Beginner’s Guide…” courses.

So, in summary, I don’t have any direct comments on a solution for you; your modelling has necessitated quite complex and hard-to-understand DAX. I’d think with a total re-write from first principles using the topics showcased in the beginner’s guide courses will set you well on your way.
Greg

[EnterpriseDNA]

Hi @RyanWhite, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @RyanWhite, we’ve noticed that no response has been received from you since the 10th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @RyanWhite, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!