Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello everyone,
I'm facing an issue with my DAX calculations for monthly cumulative totals in Power BI, and I'm hoping someone can help me figure out what I might be doing wrong. My goal is to create DAX formulas that calculate the monthly cumulative totals so that I can display an increasing line graph illustrating how my costs have increased over time.
I have two different data formats to work with:
1. In the first format (Table Name: Cost 2), the data is already aggregated to a monthly level, and there is a one-to-one relationship with the calendar table.
2. In the second format (Table Name: Cost 1), the data includes multiple dates within a month and their corresponding cost values, resulting in a many-to-one relationship with the calendar table.
I've successfully created DAX formulas to calculate the cumulative totals for the current and previous years for both formats. The DAX for 'Cost 2' works as expected, but I'm encountering issues with 'Cost 1'. Here are the DAX formulas I'm using:
For 'Cost 2':
```DAX
2022 Costs 2 =
CALCULATE(
SUM('Cost 2'[Total Cost]),
FILTER(
ALL('Cost 2'[Month]),
YEAR('Cost 2'[Month]) = YEAR(MAX('Cost 2'[Month]))-1 && MONTH('Cost 2'[Month]) <= MONTH(MAX('Cost 2'[Month]))
)
)
```
For 'Cost 1':
```DAX
2022 Costs 1 =
CALCULATE(
SUM('Cost 1'[Amount EUR]),
FILTER(
ALL('Cost 1'[Date]),
YEAR('Cost 1'[Date]) = YEAR(MAX('Cost 1'[Date]))-1 && MONTH('Cost 1'[Date]) <= MONTH(MAX('Cost 1'[Date]))
)
)
```
The issue is that the 'Cost 1' calculation is producing incorrect results. I'm puzzled as both DAX calculations are very similar, yet they behave differently. Can anyone please help me understand how to fix the DAX calculation for 'Cost 1'? Your assistance would be greatly appreciated.
Thank you in advance for your help!
Best regards,
Manish
Link to Data File and .pibx file
https://drive.google.com/drive/folders/1OcVJ3bLUzPNhZWX2wO0LSSVN_CUHSJbs?usp=sharing
Solved! Go to Solution.
I see that you've tried a different approach, but there seems to be a misunderstanding in the modified DAX. It's important to note that when working with cumulative totals in a specific month, you should use a cumulative pattern that resets the total at the beginning of each month. Here's an alternative approach to calculate the cumulative totals for 'Cost 1':
YearMonth = YEAR('Cost 1'[Date]) * 100 + MONTH('Cost 1'[Date])
Next, create a measure for the cumulative total. This measure should reset the cumulative total at the start of each month:
Cumulative Total 1 =
VAR CurrentYearMonth = MAX('Cost 1'[YearMonth])
RETURN
CALCULATE(
SUM('Cost 1'[Amount EUR]),
FILTER(
ALL('Cost 1'),
'Cost 1'[YearMonth] <= CurrentYearMonth
)
)
This measure calculates the cumulative total for 'Cost 1' by summing the 'Amount EUR' for all rows where the 'YearMonth' is less than or equal to the maximum 'YearMonth' within the current filter context.
By using this approach, you should be able to correctly calculate the monthly cumulative totals for 'Cost 1' in Power BI, and it will reset at the beginning of each month, ensuring accurate results.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
The reason you're encountering issues with the 'Cost 1' calculation is due to the difference in data structure and the way you're filtering the data. In 'Cost 2', you're dealing with a monthly aggregation, so it's relatively straightforward to calculate cumulative totals. However, in 'Cost 1', where you have multiple dates within a month, you need to consider these multiple dates when calculating cumulative totals. Your current DAX formula for 'Cost 1' doesn't account for this.
To calculate the cumulative totals for 'Cost 1' accurately, you should modify your DAX formula. You need to calculate the cumulative total for each month separately and then combine them. Here's how you can do it:
2022 Costs 1 =
SUMX(
FILTER(
ALL('Cost 1'[Date]),
YEAR('Cost 1'[Date]) = YEAR(MAX('Cost 1'[Date]))-1
),
CALCULATE(
SUM('Cost 1'[Amount EUR]),
FILTER(
ALL('Cost 1'[Date]),
YEAR('Cost 1'[Date]) = YEAR(MAX('Cost 1'[Date]))-1 &&
MONTH('Cost 1'[Date]) = MONTH(MAX('Cost 1'[Date]))
)
)
)
In this modified formula, we use the SUMX function to iterate over each month within the previous year (hence the outer FILTER). For each month, we calculate the cumulative total by summing the 'Amount EUR' for all dates within that month. This way, you correctly account for multiple dates within a month in 'Cost 1'.
Make sure to replace the '2022' year filter with the desired year, and this should provide you with the correct cumulative totals for 'Cost 1'.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Hi,
I tried the suggested DAX as well as modified it to ```
refer to the last column in the table. The cumulative aggregation is not happening properly.
I see that you've tried a different approach, but there seems to be a misunderstanding in the modified DAX. It's important to note that when working with cumulative totals in a specific month, you should use a cumulative pattern that resets the total at the beginning of each month. Here's an alternative approach to calculate the cumulative totals for 'Cost 1':
YearMonth = YEAR('Cost 1'[Date]) * 100 + MONTH('Cost 1'[Date])
Next, create a measure for the cumulative total. This measure should reset the cumulative total at the start of each month:
Cumulative Total 1 =
VAR CurrentYearMonth = MAX('Cost 1'[YearMonth])
RETURN
CALCULATE(
SUM('Cost 1'[Amount EUR]),
FILTER(
ALL('Cost 1'),
'Cost 1'[YearMonth] <= CurrentYearMonth
)
)
This measure calculates the cumulative total for 'Cost 1' by summing the 'Amount EUR' for all rows where the 'YearMonth' is less than or equal to the maximum 'YearMonth' within the current filter context.
By using this approach, you should be able to correctly calculate the monthly cumulative totals for 'Cost 1' in Power BI, and it will reset at the beginning of each month, ensuring accurate results.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Thank you this helped
You are always welcome.😊
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |