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

0
votes

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

3
powerbidax
Create a proper calendar table and use its dates, not the dates from the transactional table – RADO
@RADO : This is a quick sample that I created. My actual calendar starts from 2005 to 2020 and has many more columns. My purpose is to understand the behavior of Power BI. Why it skips the rows for which data is not available and how to overcome this. I tried using IF(contains(Table,Table[sale] ,selectedvalue(table[sale]) = blank(),0,1). This is also not working – sam

3 Answers

2
votes

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

0
votes

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

0
votes

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