You’re the best I’ve came across btw. Explanations, definitions, professional terse intro with oblivious depth and passion is how I like it. Your dashboard training pretty much catapulted my career. My running totals stop at month ends, then rests every month. My lookup date column is week ending dates only. I wonder if that’s why. John Texas
Wow, that's amazing to hear, John! Not sure what's causing your RT to reset each month. You can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I’ve seen many masters in excel…but you are outstanding. You are doing with so confidently, we have to pause the video to understand. Curious to know how long you are using this excel ?
Thanks for your kind words 😊 I’ve been using Excel for nearly 30 years 😉 but really my skills improved significantly when I started teaching others about 12 years ago.
I found this formula helpful, but ended up having my running totals go to zero in months where I had no data, then reappearing in months with activity. Any recommendation on how to work around this?
Great video! For me it looks like a bug in the traditional pivot chart function but nevermind. Here is the formula shown in the video : =calculate(SUM(Table_with_data[value_field]),FILTER(ALLSELECTED(your_calendar[date]),ISONORAFTER(your_calendar[date],max(table_with_data[date]),desc))) It works also for cumulative count of values by replacing SUM in the formula with COUNT (or COUNTA for non numerical values)
This is excellent. May in know how you do the running total of subtotals. Lets say for a product over months(horizintal) if you need to show data like for first month, the first value, for second Month value of first month plus value of 2nd Month and for 3rd month value of 1st plus value of 2nd plus value of 3rd and so on...
I'm a beginner of Power Pivot: I downloaded the Excel file but didn't understand when and where you created the calendar table, I found it directly in data model but it doesn't exist in Excel sheets. Is it possible?
On the Design tab you can create a date table based on the dates in the model via the Date Table drop down > New. If you don't see this option then you have an old version of Excel that doesn't have it. If so, you can create one in Power Query or Excel.
Nice, thanks! Mynda, I couldn't watch the whole video since it gave an error on Mac that It will not work properly on Mac. My question to you is how to take out Total of Running Total? I have created my Pivot Table and got the Running Total but now at the end, I want it to show Total of Running Total? Thanks
The total of a running total is the same as the column total, so you don't need a running total to get this. Hopefully, I've understood your question. If not, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Simply amazing. Love hour channel. Question: how would one get the beginning -->month activities --> ending running running subtotals In pivot tables? Subject for another video ? 😁
Hi Mynda, this running total measure DAX is really helpful. But how could I create a measure to calculate the difference of two consecutive rows in a power pivot table, say next row’s value minus current one? (My case is - my data table has records only when the plant generates data, thus the date or time is not continuous, or I will have some empty row if I use my calendar table to summarise data, as far as I know I cannot use DAX function like PREVIOUSDAY, EARLIER, etc). I am still not familiar with DAX functions, so could you make an episode to address the above mentioned issue for calculating difference of two role in a power pivot table by using DAX function. Thanks!
Hi Mynda Great video! I've searched around a bit now and only find examples similar to yours. I have a problem to run totals by date and product. I've tried to combine different DAX formulas but the runing totals with DAX only work when I exclude product. I would really appreciate and hope other will to it too if you can guide or help me. Example: In the end of your demonstration you made in the pivottable you, dragged and drop dates to "row" and dragged and drop measures to "Values" field How about when you drag and drop anydata (for.eg products) to "columns" Best Regards
Hi Jill, I don't have any tutorials specifically on ISONORAFTER, but perhaps this breakdown of the function will help: learn.microsoft.com/en-us/dax/isonorafter-function-dax
Hi Mynda, Great video! One difference to the built in Show values as 'Running Total In..' is that the built in version repeats the value from the last month if the next month does not have any actuals values (or forecast or budget), while your version leaves an empty row and a gap in the graph. For example, if you remove the values for actuals and forecast for August 2020 in your example, you get a gap in the graph. Any way to fix the measure to keep the value from last month instead? David
Hi David, Yes, unfortunately, this formula doesn't allow for gaps in your dataset. I don't have a tutorial that explains the solution, so I recommend you post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda, a SOS question 🙋♀️ what if I want to calculate the running total of the last 4 weeks for a MEASURE value? I tried multiple ways, and couldn’t get it. Could you please help me?
Great video! If there are two dates in the table i.e. Order date and shipped date, thus making one active and one inactive relationship, how can we get the RT result? Where do we place the USERELATIONSHIP formula? Thanks in advance!
Thank you! In your example, what if you have forecast in 2022 but no actuals. How do you show zero for actuals RT in 2022 instead of repeating 301,845 . I would like to show where the RT stops in a year and not repeat the values.
Hi Mynda, how did you get the option for Actual(Date)? I assume that's referring to an Actual column in the date table? When I create a date table it doesn't add data columns into it.
PivotTables aggregate data. If you don't want to see the data aggregated, then you need to bring in the fields from all the columns to the row labels until such time as it is no longer able to aggregate any of the values.
Hi Mynda, When using this formula for a measure on a data set that is really long (160k+ rows) it takes a very long time to load. Is there a more complex formula/filter I can apply to make sure it is not looking at all of the data (every row) to make the chart/table? Currently it take 3 minutes to make the chart.
hi, I want to make a RunningTotal using Pivot table, i've the following data: id Customer item SalesAmount 1 Global inc apple 50 2 Americana banana 10 3 Americana apple 100 4 Americana apple 60 5 Global inc apple 10 it's a very simple pivot table with the "id" field in the Rows section, and the "item" in the Filters section, and the "SalesAmount" in the Values section, i set the "Show Value As" of the SalesAmount column "Value Filed settings" to "Running Total in" and set the Base Field to "id" and it works fine, but i also want to show the customer, so i add this field to the Rows section after the "id" Field but as soon as i do this the running total column becomes distorted, as if the summing process reset when the customer change, or Excel auto group the data according to the customer despite that i set the Base Field of the running total column to id.
It's not always wrong, so there must be something specific to your scenario. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
It’s a great video, but I can’t believe that something so basic and simple as a running total in Excel is so complicated. There is no way an average user would know how to write that measure, I expect everyone that watches this video copies it from your video.
