Forum Discussion

Maclurad's avatar
Maclurad
Copper Contributor
Feb 07, 2025

Excel SUMIFS(): Calculate sum in point of time of negative balance components only.

Hi community,
I'm not an Excel expert, so I get confused when trying to accomplish even the most trivial calculation.
And for this one, I've already spent too much time trying and searching online, without success. Even three different AI engines didn't give me clear answers. It seems like no one has ever stumbled upon this scenario, strange.
So, this is my sample dataset:

Please note my dataset uses "date smart IDs" instead of "date" datatypes, but nothing changes, once you know that Date_2_SID = -1 defaults to 1900-01-01 in the corresponding date format.

Now, suppose we need to calculate the Total Overall Balance (which is the SUM of each row Value 1 - Value 2) at my reporting date which is 2022-12-31.
Result:

Then, suppose we need to add an additional metric to show the Total Negative Components Balance (which is the SUM of each row Value 1 - Value 2 only when Value 1 - Value 2 < 0) at my reporting date which is again 2022-12-31.
Result:

Both above calculations seem straightforward in this case, but there is an additional constraint we should consider:

Each operation (Value 1 - Value 2) can only be performed if Date_1_SID AND Date_2_SID are both before or on the same day of the reporting date . 

This condition is met for all the dates in the above dataset if my reporting date is >= 2022-12-15.

But what if I need to make the above calculation available for any date in the Gregorian calendar? Or, in other words, if my reporting date is "any" date?

It's a bit more triky...

For semplicity let's consider we need to present the "Overal Balance" for each end of month of year 2022, and let's assume our "reference date" for "reporting date" is Date_1_SID which is always populated in the above dataset.

This is the result:

We used the Excel function SUMIFS() in this way:

Overall Balance @ Reporting date = 
     SUMIFS( 
          Value_1_Range, 
          Date_1_SID_Range, "<=" & Reporting_Date_SID 
     ) - 
     SUMIFS( 
          Value_2_Range, 
          Date_1_SID_Range, "<=" & Reporting_Date_SID, 
          Date_2_SID_Range, "<=" & Reporting_Date_SID 
     )

And now my question:

How to calculate in Excel the "Total Negative Components Balance" for the same reporting dates?

If I'm not mistaken, this should be the expected result:

PS: I wanted to solve this in Excel first, to learn the lesson, but then I need to implement the above logic in DAX since Excel cannot manage the size of my dataset made of millions of rows. So a DAX answer would work too.

BI & Data Analysis
excel
Formulas and Functions
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Feb 08, 2025

    Not sure I understood the logic correctly.

    First, I'd convert Date ID to dates applying Date1 to Date2 if the latest is -1. Next, add calendar table to the model and create relationships.

    With that measures could be

    Overall Balance :=
VAR selectedDate =
    MAX ( 'Date'[Date] )
VAR sum1 =
    CALCULATE ( SUM ( Source[Value 1] ), 'Date'[Date] <= selectedDate )
VAR sum2 =
    CALCULATE (
        SUM ( Source[Value 2] ),
        'Date'[Date] <= selectedDate,
        USERELATIONSHIP ( Source[Date 2 SID], 'Date'[Date] )
    )
RETURN
    sum1 - sum2 - 0
////////
Negative montly :=
VAR selectedDate =
    MAX ( 'Date'[Date] )
VAR selectedMonth =
    FORMAT ( selectedDate, "MMM-YYYY" )
VAR s1 =
    CALCULATE ( SUM ( Source[Value 1] ), 'Date'[Month Year] = selectedMonth )
VAR s2 =
    CALCULATE (
        SUM ( Source[Value 2] ),
        'Date'[Month Year] = selectedMonth,
        Source[Value 1] = 0,
        USERELATIONSHIP ( Source[Date 2 SID], 'Date'[Date] )
    )
RETURN
    IF ( s1 < s2, s1 - s2, 0 )
///////
Negative Components Balance :=
VAR selectedDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE ( SUMX ( 'Date', [Negative montly] ), 'Date'[Date] <= selectedDate )

    which gives

    Please check in attached

Resources