Forum Discussion
Add Column with DAX function
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"
) )
Community ChampionHi 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.
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
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"
) )
