Untitled

 avatar
unknown
plain_text
5 months ago
1.3 kB
3
Indexable
Value Difference (Raw) = 
SUMX(
    FILTER(
        CROSSJOIN(
            DISTINCT('Aggregated Value_SQL'[BMU]),
            DISTINCT('Aggregated Value_SQL'[Settlement Day]),
            DISTINCT('Aggregated Value_SQL'[Settlement Period])
        ),
        NOT(ISBLANK([BMU]))
    ),
    VAR AggValue = 
        CALCULATE(
            SUM('Aggregated Value_SQL'[+ve Agg Value]),
            'Aggregated Value_SQL'[BMU] = EARLIER([BMU]),
            'Aggregated Value_SQL'[Settlement Day] = EARLIER([Settlement Day]),
            'Aggregated Value_SQL'[Settlement Period] = EARLIER([Settlement Period])
        )
    VAR GenSentOut = 
        CALCULATE(
            SUM('QM & Generation sent out_SQL'[Generation Sent Out]),
            'QM & Generation sent out_SQL'[BMU] = EARLIER([BMU]),
            'QM & Generation sent out_SQL'[Settlement Day] = EARLIER([Settlement Day]),
            'QM & Generation sent out_SQL'[Settlement Period] = EARLIER([Settlement Period])
        )
    RETURN 
        -- Return the absolute difference, not subtraction
        IF(
            ISBLANK(AggValue) && ISBLANK(GenSentOut), 
            BLANK(),  -- Ignore cases where both values are blank
            COALESCE(AggValue, 0) - COALESCE(GenSentOut, 0)
        )
)
Editor is loading...
Leave a Comment