DAX Calculation Discrepancy in Monthly Cumulative Totals for Different Data Formats

[manish-tripathi]

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 

[123abc]

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.

[manish-tripathi]

Hi,

I tried the suggested DAX as well as modified it to ```

2022 Costs 1.1 =

SUMX(

FILTER(

ALL('Cost 1'[Date]),

YEAR('Cost 1'[Date]) = 2022

),

CALCULATE(

SUM('Cost 1'[Amount EUR]),

FILTER(

ALL('Cost 1'[Date]),

YEAR('Cost 1'[Date]) = 2022 &&

MONTH('Cost 1'[Date]) = MONTH(MAX('Cost 1'[Date]))

)

)

)```

However I still am not getting the desired result

refer to the last column in the table. The cumulative aggregation is not happening properly.

[123abc · Accepted 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':

1. First, create a calculated column in your 'Cost 1' table to extract the year and month from the 'Date' column. This column will be used to group your data by year and month. You can use the following DAX formula:

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.

1. Now, you can use this 'Cumulative Total 1' measure in your report to display the cumulative totals for 'Cost 1' on your visualizations.

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.

[manish-tripathi]

Thank you this helped

[123abc]

 You are always welcome.😊