Forum

How do I calculate ...
 
Share:
Notifications
Clear all

How do I calculate the cumulative total in Power BI using DAX? Provide an example with sample data and explanation.


Posts: 97
Admin
Topic starter
(@sql-admin)
Estimable Member
Joined: 5 years ago

Description:

In Power BI, calculating the cumulative total up to a specific date is a common requirement for many reporting needs. This DAX formula helps you easily achieve that. Whether you're tracking sales, revenue, or any other metric over time, learning how to calculate the cumulative total can enhance your data analysis skills.

Answer:

To calculate the cumulative total in Power BI using DAX, you can use the CALCULATE function along with FILTER and ALL. Here's a step-by-step explanation:

Example DAX Formula:

CumulativeTotal =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL(Sales[Date]),
Sales[Date] <= MAX(Sales[Date])
)
)

Explanation:

  • SUM(Sales[Amount]): Sums up the sales amount.
  • FILTER(ALL(Sales[Date]), Sales[Date] <= MAX(Sales[Date])): Filters all dates up to the current date in the context of the calculation.
  • CALCULATE: Modifies the filter context to apply the cumulative total calculation.

Sample Data:

Let's consider a sample sales table:

Date Amount
2024-05-01 100
2024-05-02 150
2024-05-03 200
2024-05-04 250

Using the above DAX formula, the cumulative total would be calculated as follows:

Date Amount CumulativeTotal
2024-05-01 100 100
2024-05-02 150 250
2024-05-03 200 450
2024-05-04 250 700

The CumulativeTotal column shows the running total of the sales amount up to each date.

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: