Sum(amount) for Account ids there on Yesterday but those account ids do not exist day before yesterd

[NandhiniE31]

Hi, 

I have issue with a DAX where I have to find total sales for account IDs not present Day-2( Day before yesterday) but present Day-1 (Yesterday) based on date from filter. 

Table Structure: ACCOUNT_ID ,DATE, SALES AMOUNT

Sample Table: 

Date	Acc_ID	SalesAmount
19-12-2017	1	10
18-12-2017	1	20
19-12-2017	2	10
19-12-2017	3	50
17-12-2017	4	100
19-12-2017	4	40
18-12-2017	4	25
17-12-2017	1	15

Example: if the date selected is 20/12/2017 , Sum of sales amount should be 60.

[AnkitBI · Accepted solution]

I have come up with below. Please check. I have created a new date table for slicing. You can modify DAX if you don't have any date table with you.

Measure 3 = 
var p1 =  FILTER(all(Table1),Table1[Date] = SELECTEDVALUE(Table3[Day]) - 1)
var r1 = SELECTCOLUMNS(p1,"acct",Table1[Acc_ID])
var p2 = FILTER(all(Table1),Table1[Date] = SELECTEDVALUE(Table3[Day]) - 2)
var r2 = SELECTCOLUMNS(p2,"acct",Table1[Acc_ID])
var r3 = EXCEPT(r1,r2)
var r4 = CALCULATE(sum(Table1[SalesAmount]),filter(all(table1),Table1[Acc_ID] in distinct(r3) && Table1[Date] = SELECTEDVALUE(Table3[Day]) - 1 ))
return 
r4