Forum Discussion

peterhui50's avatar
peterhui50
Helper III
4 years ago
Solved

Subtraction using the same column

Hi,   I have a table here, in Power BI, I am using a table visual, Test_A and Test_B are measures and Test_C is a subtraction both.   but basically I want it to do this..     I want the...
General Comment
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi peterhui50 

    try this

    Test_C = 
VAR _date1 =
    DATE ( 2020, 3, 31 )
VAR _date2 =
    DATE ( 2021, 3, 31 )
VAR _value1 =
    CALCULATE (
        MIN ( 'Table'[Units_Sold] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Company] ),
            'Table'[Date] = _date1
        )
    )
RETURN
    IF (
        MIN ( 'Table'[Date] ) = _date1,
        MIN ( 'Table'[Units_Sold] ),
        IF (
            MIN ( 'Table'[Date] ) = _date2,
            MIN ( 'Table'[Units_Sold] ) - _value1,
            BLANK ()
        )
    )

    result

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

v-xiaotang's avatar
v-xiaotang
Community Support
4 years ago

Hi peterhui50 

try this

Test_C = 
VAR _date1 =
    DATE ( 2020, 3, 31 )
VAR _date2 =
    DATE ( 2021, 3, 31 )
VAR _value1 =
    CALCULATE (
        MIN ( 'Table'[Units_Sold] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Company] ),
            'Table'[Date] = _date1
        )
    )
RETURN
    IF (
        MIN ( 'Table'[Date] ) = _date1,
        MIN ( 'Table'[Units_Sold] ),
        IF (
            MIN ( 'Table'[Date] ) = _date2,
            MIN ( 'Table'[Units_Sold] ) - _value1,
            BLANK ()
        )
    )

result

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • peterhui50's avatar
    peterhui50
    Helper III
    4 years ago

    Thanks for your review xiaotang, really appreciate it.

     

    I found the measure now, it was in an archived thread. I can't take credit for it, but here it is.

     

    Test_Earlier = 

CALCULATE (
    SUM ( 'Table'[Units_Sold] ),
    FILTER (
        ALLSELECTED('Table') ,
        'Table'[YEAR]
            = SELECTEDVALUE ( 'Table'[YEAR] ) - 1
            && 'Table'[Company] == SELECTEDVALUE ( 'Table'[Company] )
    )
)

     

    The result is this..