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,...
BI & Data Analysis
excel
Formulas and Functions
power bi
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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