CODEWITHSUNDEEP
powerbidax
sam
sam

Reputation: 1304

FILTER DAX function is missing the rows in cases where sales for previous DAY is not available

I have to show the previous day sale along with the current sale. The problem is that the day which does not have previous day sale, that row is getting skipped. I am using the below DAX function :

SumInRange = 
var sel = selectedvalue('Table'[Date])
return
CALCULATE(SUMX(DISTINCT('Table'[Date]),CALCULATE(MAX('Table'[Sale]))),FILTER(all('Table'),'Table'[Date] = sel -1))

To replicate the scenario first step is to create Dim Date table using - >

Dim Date = GENERATESERIES(date(2019,01,01),date(2019,12,31),1)

second Step is to create DataTable ->

Table = DATATABLE("Date",DATETIME,"Flag1",STRING,"Flag2",STRING,"Sale",INTEGER,{
{"8/1/2019","True","True",200},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"9/3/2019","False","True",60},
{"9/4/2019","False","True",10},
{"9/5/2019","False","True",100},
{"9/6/2019","False","True",30},
{"9/7/2019","False","True",60},
{"9/8/2019","False","False",150},
{"9/9/2019","False","False",80},
{"9/10/2019","False","False",90},
{"9/11/2019","False","False",30},
{"9/12/2019","False","False",20},
{"10/13/2019","False","True",50},
{"10/14/2019","False","True",60},
{"10/15/2019",BLANK(),BLANK(),BLANK()},
{"10/16/2019",BLANK(),BLANK(),BLANK()}
})

3rd Step - create a relation between these tables on date column

4th step - create Measure using -

SumInRange = 
var sel = selectedvalue('Table'[Date])
return
CALCULATE(SUMX(DISTINCT('Table'[Date]),CALCULATE(MAX('Table'[Sale]))),FILTER(all('Table'),'Table'[Date] = sel -1))

Output of this is attached as the screenshot. You will observe that the date that does not have previous day sale is getting omitted like - 08/01/2019. Row for this date is getting skipped because it does not have previous day sale? How to get the skipped row back again giving 0 sale for previous date?

enter image description here

Upvotes: 0

Views: 983

Answers (3)

Stachu
Stachu

Reputation: 1724

You should use a measure for this, not a calculated column. Also SUMX is a definite overkill - you unnecessarily iterate over every row in Table

SumInRange = CALCULATE(SUM('Table'[Sale]),DATEADD('Dim Date'[Value],-1,DAY))

enter image description here

Upvotes: 2

Himanshu Agrawal
Himanshu Agrawal

Reputation: 271

Another way to achieve this without changing your existing formula is by enabling the option "Show items with no data" for the Date attribute in visualization pane. Please see the screen shot for reference.

enter image description here

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

You can just create a new custom column in your table "Table" with below code. This new column will hold your expected results-

prev_day_sale = 

VAR yesterday = DATEADD('Table'[Date],-1,DAY)

VAR prev_day_sale = 
LOOKUPVALUE(
    'Table'[Sale],
    'Table'[Date], yesterday
)

RETURN IF(
    prev_day_sale = BLANK(),
    0,
    prev_day_sale
)

Output will be as below-

enter image description here

Upvotes: 0

Related Questions