Add Column with DAX function

[pjr1221]

Hello All, 

How can I add column using DAX? I don't know what function to use in DAX. 

Here is sample table,I can't show my real table because it's too big.

I want add <GAP> column.

The formla is in the same <ID>, add differences value compared with previous week value.

below table is what i looking for table.

It is too difficult for me.. i'm beginner 
Please help me.

[Anonymous]

GAP =
VAR _ValuePreviousWeek =
    CALCULATE (
        DISTINCT ( Table1[Value] );
        ALLEXCEPT ( Table1; Table1[ID] );
        Table1[WEEK] = EARLIER ( Table1[WEEK] ) - 1)
RETURN
    IF ( NOT ISBLANK ( _ValuePreviousWeek ); Table1[VALUE] - _ValuePreviousWeek )

[AlB]

Hi @pjr1221 

Try this for your new calculated column. Table1 is the name of the table you show

GAP =
VAR _ValuePreviousWeek =
    CALCULATE (
        DISTINCT ( Table1[Value] );
        ALLEXCEPT ( Table1; Table1[ID] );
        Table1[WEEK] = EARLIER ( Table1[WEEK] ) - 1
    )
RETURN
    IF ( NOT ISBLANK ( _ValuePreviousWeek ); Table1[VALUE] - _ValuePreviousWeek )

On a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.

[pjr1221]

@AlB Thank you so much, AIB. In addition, if the difference is increased by more than 20%, I would like to enter "Increase", if it decreases by more than 20%, I would like to enter "Decrease". but what should I do?

Thank you very much for your help.

[AlB · Accepted solution]

@pjr1221 

Edited:

I took the last line away. That was causing the variant data type error. You haven't described what you need if the result is neither >20% above or <20% below. The code below returns blank in those cases. If that's no what you need update it accordingly.

GAP_v2 =
VAR _ValuePreviousWeek =
    CALCULATE (
        DISTINCT ( Table1[Value] );
        ALLEXCEPT ( Table1; Table1[ID] );
        Table1[WEEK]
            = EARLIER ( Table1[WEEK] ) - 1
    )
RETURN
    IF (NOT ISBLANK ( _ValuePreviousWeek );
        VAR _Perc =
            DIVIDE ( Table1[VALUE] - _ValuePreviousWeek; _ValuePreviousWeek )
        RETURN
            SWITCH (
                TRUE ();
                _Perc >= 120 / 100; "Increase";
                _Perc <= 80 / 100; "Decrease"
            )
    )

[pjr1221]

@AlB  If the result is neither >20% above or <20% below, I would like to enter "Null".

[pjr1221]

Hi, @AlB  I've applied it to my data, it's not working.

error is :

Expressions that yield variant data-type cannot be used to define calculated columns.