topic Re: Incorrect SUM in DAX Commands and Tips

Incorrect SUM

Adamkowalsky92 — Thu, 06 Feb 2025 05:40:39 GMT

Hey, I need help with a simple formula. At first glance dax I wrote it is simple and in the lines it works correctly however the sum no longer match.

The formula is supposed to search for the max date with the data and subtract the previous day, but since the lines have different max dates, I want the sum of these differences, not the total for example 05.02 - 04.02

As in the image below from the green values, the red values should be subtracted. The expected result for the total is 55, with -125 received, for obvious reasons.

Thanks for help

Re: Incorrect SUM

bhanu_gautam — Thu, 06 Feb 2025 06:42:19 GMT

Adamkowalsky92 , Can you share formula which you have used

Re: Incorrect SUM

Adamkowalsky92 — Thu, 06 Feb 2025 07:38:18 GMT

Parameter define how much days i want back

Re: Incorrect SUM

bhanu_gautam — Thu, 06 Feb 2025 07:56:44 GMT

Adamkowalsky92 Try using

dax
VAR maxday = MAX(Sales[Day])
VAR beforeday = CALCULATE(MAX(Sales[Day]), Sales[Day] < maxday)
VAR sales = CALCULATE(SUM(Sales[Value]), Calendar[Date] = maxday)
VAR salesbefore = CALCULATE(SUM(Sales[Value]), Calendar[Date] = beforeday)
RETURN
SUMX(
VALUES(Sales[Day]),
sales - salesbefore
)

Re: Incorrect SUM

Adamkowalsky92 — Thu, 06 Feb 2025 09:25:30 GMT

Unfortunately, this is not a suitable solution:

1. you have not added a parameter option that defines the value by a certain number of days
2. I want the sum of only one day, not all earlier than max date

Re: Incorrect SUM

Anonymous — Fri, 07 Feb 2025 01:45:36 GMT

Hi Adamkowalsky92 ,
I tried to reproduce your visual object, but was unsuccessful. However, I think this problem can be solved quickly. To better help you solve the problem, could you provide some example pbix? Thank you very much for your cooperation!

Re: Incorrect SUM

Adamkowalsky92 — Fri, 07 Feb 2025 05:54:44 GMT

I resolves problem myself ny using

SUMX(
SUMMERIZE(
Sales,Sales[Client],"Sum",CALCULATE(SUM(Sales[Value]),Sales[Date]=MAX(Sales[Date]))),Sum)

_

SUMX(
SUMMERIZE(
Sales,Sales[Client],"Sum",CALCULATE(SUM(Sales[Value]),Sales[Date]=MAX(Sales[Date])-1)),Sum)