Need help to create a DAX query for a column

[sreeprasad]

Hi,

I have a table with few columns. I want to create a column by checking check whethere the shop is "ADD_TO_CART" THEN Number, when shop is "PRODUCT_VIEW" THEN the Number of ADD_TO_CART- Number of PRODUCT_VIEW, WHEN shop is "CHECKOUT"  THEN the Number of PRODUCT_VIEW- Number of CHECKOUT. Could anyone please help me on this?

[v-danhe-msft · Accepted solution]

Hi @sreeprasad,

Based on my test, you could refer to below formula:

Column = IF([Shop]="PRODUCT_VIEW",
    CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="ADD_TO_CART"&&'Table1'[Date]=EARLIER(Table1[Date])))-CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="PRODUCT_VIEW"&&'Table1'[Date]=EARLIER(Table1[Date]))),
         IF([Shop]="CHECKOUT",
             CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="PRODUCT_VIEW"&&'Table1'[Date]=EARLIER(Table1[Date])))-CALCULATE(SUM(Table1[Number]),FILTER('Table1','Table1'[Shop]="CHECKOUT"&&'Table1'[Date]=EARLIER(Table1[Date])))
             ,[Number]))

Result:

You could also download the pbix file to have a view:

https://www.dropbox.com/s/3jw6hqb5o4ib8uh/Need%20help%20to%20create%20a%20DAX%20query%20for%20a%20co...

Regards,

Daniel He