Calculating data for the day before the selected plus additional filter

[guineapig]

Hi all! I have an issue with creating a report and will be thankful for any help.

I have two tables:

1) Data

Date               Id        AmountUSD  Type 

2019-10-01    111      1000               1

2019-10-01    112      10                   0

2019-10-02    113      500                 0

2019-10-03    114      400                 0

2) DateTimeTable

Actually, datetimetable with one column Date with distinct values for dates

I have a date slicer and two card visuals: one for the sum of all amounts from date before the selected and second for the difference between  sum of all amounts with type 0 between selected date and date before the selected.

I'm using following measure:

Sum of all amounts from date before the selected = 

var mydate=SELECTEDVALUE('DateTimeTable'[Date])

return

CALCULATE(SUM(Data[AmountUSD]), FILTER(ALL('DateTimeTable'), DateTimeTable[Date]=mydate-1))

and it works perfectly!!! but when I'm trying to add one more filter clause it shows "blank"

Sum of all amounts from date before the selected with type 0 = 

var mydate=SELECTEDVALUE('DateTimeTable'[Date])

return

CALCULATE(SUM(Data[AmountUSD]), FILTER(ALL('DateTimeTable'), DateTimeTable[Date]=mydate-1), FILTER('Data', 'Data'[type]=0))

And for sure there is data with  type "0" for each date. Also this measure works perfectly (logically, of course I don't need that filter with date here, but still):

Sum of all amounts from selected date with type 0 = 

var mydate=SELECTEDVALUE('DateTimeTable'[Date])

return

CALCULATE(SUM(Data[AmountUSD]), FILTER(ALL('DateTimeTable'), DateTimeTable[Date]=mydate), FILTER('Data', 'Data'[type]=0))

I cannot understand how this works((( Please, any help will be highly appreciated.

[amitchandak]

You have mentioned

"2) DateTimeTable

Actually, date timetable with one column Date with distinct values for dates

"

Make sure your date timetable is continuous table else use power BI calendar command to generate the calendar.

Try to take a max of the selected value. This should even work without selected value.

Var   _end_date=(maxx('Date','Date'[Date])) 

Use this in =.

Also, test the data without date filter  check that another filter is working fine.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

[guineapig]

@amitchandak , got it, sorry. so I've tried MAX function, same blank value

[amitchandak]

Is it possible to share sample pix after removing sensitive information?

[guineapig · Accepted solution]

thanks a lot! it seems I finally found a solution. now I'm calculating value for the day BEFORE THE SELECTED like that:

var mydate=SELECTEDVALUE(DateTimeTable[date])

return

CALCULATE(SUM(Data[AmountUSD]), ALL(DateTimeTable), 'Data'[type]=0, 'Data'[date]=mydate-1)

I have no idea what was wrong with the first attempt, also second attempt and n-th attempt, but this one works perfectly. 

[v-chuncz-msft]

@guineapig 

By the way, you may help accept solution. Your contribution is highly appreciated.

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

[guineapig]

---

@amitchandak wrote:

You have mentioned

"2) DateTimeTable

Actually, date timetable with one column Date with distinct values for dates

"

Make sure your date timetable is continuous table else use power BI calendar command to generate the calendar.

Try to take a max of the selected value. This should even work without selected value.

Var   _end_date=(maxx('Date','Date'[Date])) 

Use this in =.

Also, test the data without date filter  check that another filter is working fine.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

---

Yep, I'm using calendar command for creating date tables. 

Try to take a max of the selected value. This should even work without selected value. - Could you please explain this? Cause I'm receiving an error "too few argument were passed to MAXX function"

yep, I've tested my filter, both filters works perfectly without each other)

@amitchandak 

[guineapig]

ok, I managed to solve this by changing date slicer from datetimetable to data table. So now I'm slicering by date column in Data Table. My final query looks like:

diff between selected date and date before the selected =

var mydate=SELECTEDVALUE('Data'[Date])

return

CALCULATE(SUM(Data[AmountUSD]), FILTER(ALL('Data'), Data[date]=mydate && 'Data'[type]=0))-

CALCULATE(SUM(Data[AmountUSD]), FILTER(ALL('Data'), Data[Date]=mydate-1 && Data[type]=0))

But what if I need to add more tables and to use a datetimetable for slicer? How to filter on several tables?