[Power BI] Running Total by date Or/And by product
Most of the time we would like to know the running(cumulative) total of your sales. Here I will show you how to do that in three different scenarios.
Case
Let’s say you have five products (A-E) and the sold quantity (Sold QTY) of each product by date. Below is the picture that shows Sold QTY by date and by product.
You would like to know three scenarios.
(1) Running (Cumulative) Sold QTY by Date
(2) Running (Cumulative) Sold QTY for the past 7 Days by Date
(3) Running (Cumulative) Sold QTY for the past 7 Days by Date and by Product
Scenario 1: Running (Cumulative) Sold QTY by Date
Think about how we can get the running total. If we can just sum up all the Sold QTY that is before the “current day”, it would be perfect and easy.
- Step 1: We need to get the “current date”.
- Step 2: We need a table with Sold QTY and Dates before current date
This is a Virtual Table (Dynamic Table). - Step 3: To sum up all the sold qty using the Virtual Table
Scenario 2: Running (Cumulative) Sold QTY for the past 7 Days by Date
Think about how we can get the running total. If we can just sum up all the Sold QTY that is between the first date and the last date (current day), it would be perfect.
- Step 1: We need to get the “current date” and the “first day of the 7 days”
- Step 2: We need a table with Sold QTY and the specific 7 days
This is a Virtual Table (Dynamic Table). - Step 3: To sum up all the sold qty using the Virtual Table
Scenario 3: Running (Cumulative) Sold QTY for the past 7 Days by Date and by Product
Think about how we can get the running total. If we can just sum up all the Sold QTY that is between the first date and the last date (current day), it would be perfect.
- Step 1: We need to get the “current date” and the “first day of the 7 days”
- Step 2: We need to know which product we are calculating now.
- Step 3: We need a table with Sold QTY , the specific 7 days, and products
This is a Virtual Table (Dynamic Table). - Step 4: To sum up all the sold qty using the Virtual Table
Conclusion
It can be easy to imagine a virtual table and the criteria we need first. Below please see the different results.
