How to create a calculated column in DAX, based on one column values and on a specific date of other column?

Question

I have a table with few columns. Considering each distinct date, I want to create a calculated column based on the following rules:

If [Shop] is "ADD_TO_CART" then [Number]
If [Shop] is "PRODUCT_VIEW" then [Number] of "ADD_TO_CART" minus [Number] of "PRODUCT_VIEW"
If [Shop] is "CHECKOUT" then [Number] of "PRODUCT_VIEW" minus [Number] of "CHECKOUT".

I have tried in different ways, but couldn't succeed.

table with data

This is how I have tried.

IF([Shop]="ALL_VISITS",0,
       IF([Shop]="PRODUCT_VIEW",
           CALCULATE(SUM(Number),FILTER(TableName,
           [Shop]="ALL_VISITS"))-Number,
       IF([Shop]="ADD_TO_CART",
            CALCULATE(SUM(Number),
            FILTER(TableName,[Shop]="PRODUCT_VIEW"))-Number,
       IF([Shop]="CHECKOUT",
            CALCULATE(SUM(Number),
            FILTER(TableName,[Shop]="ADD_TO_CART"))-Number,
       IF([Shop]="TRANSACTION",
            CALCULATE(SUM(Number),
            FILTER(TableName,[Shop]="CHECKOUT"))-Number,0)))))

This question feels a bit vague to me, and rather brief. I wonder, would it help readers if you were to show the way(s) in which you tried to do this thing? That might throw some light on what you are trying to do and where it is going wrong. — halfer

ptfaferreira ptfaferreira · Accepted Answer · 2018-09-06T20:56:49

For your expected output I tried successfully this SWITCH in DAX. Considering that Table1 have your columns.

Output = SWITCH([Shop];"ADD_TO_CART";Table1[Number];"PRODUCT_VIEW";CALCULATE(CALCULATE(SUM(Table1[Number]);FILTER(Table1;Table1[Shop]="ADD_TO_CART"));ALLEXCEPT(Table1;Table1[Date]))-CALCULATE(CALCULATE(SUM(Table1[Number]);FILTER(Table1;Table1[Shop]="PRODUCT_VIEW"));ALLEXCEPT(Table1;Table1[Date]));"CHECKOUT";CALCULATE(CALCULATE(SUM(Table1[Number]);FILTER(Table1;Table1[Shop]="PRODUCT_VIEW"));ALLEXCEPT(Table1;Table1[Date]))-CALCULATE(CALCULATE(SUM(Table1[Number]);FILTER(Table1;Table1[Shop]="CHECKOUT"));ALLEXCEPT(Table1;Table1[Date])))

How to create a calculated column in DAX, based on one column values and on a specific date of other column?

1 Answers