[Power BI] Running Total by date Or/And by product

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.

Sold QTY 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 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 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 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.

Scenario 1 & 2
Scenario 3. By Date and by Product

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store