Untitled

 avatar
unknown
plain_text
5 months ago
1.5 kB
3
Indexable
Value Difference (Actual) = 
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 
        -- Compute the actual difference
        IF(
            ISBLANK(AggValue) && ISBLANK(GenSentOut),
            BLANK(), -- Ignore if both are blank
            IF(
                ISBLANK(AggValue),
                GenSentOut, -- If AggValue is blank, take GenSentOut
                IF(
                    ISBLANK(GenSentOut),
                    AggValue, -- If GenSentOut is blank, take AggValue
                    AggValue - GenSentOut -- Perform subtraction if both exist
                )
            )
        )
)
Editor is loading...
Leave a Comment