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.

1 Posts
1 Users
0 Reactions
897 Views
Posts: 134
Admin
Topic starter
(@sql-admin)
Reputable Member
Joined: 6 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:

DateAmount
2024-05-01100
2024-05-02150
2024-05-03200
2024-05-04250

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

DateAmountCumulativeTotal
2024-05-01100100
2024-05-02150250
2024-05-03200450
2024-05-04250700

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: