cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
guineapig Frequent Visitor
Frequent Visitor

Calculating data for the day before the selected plus additional filter

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.
1 ACCEPTED SOLUTION

Accepted Solutions
guineapig Frequent Visitor
Frequent Visitor

Re: Calculating data for the day before the selected plus additional filter

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. 

View solution in original post

7 REPLIES 7
guineapig Frequent Visitor
Frequent Visitor

Re: Calculating data for the day before the selected plus additional filter

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? 
amitchandak Super Contributor
Super Contributor

Re: Calculating data for the day before the selected plus additional filter

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 Frequent Visitor
Frequent Visitor

Re: Calculating data for the day before the selected plus additional filter


@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 Frequent Visitor
Frequent Visitor

Re: Calculating data for the day before the selected plus additional filter

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

amitchandak Super Contributor
Super Contributor

Re: Calculating data for the day before the selected plus additional filter

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

guineapig Frequent Visitor
Frequent Visitor

Re: Calculating data for the day before the selected plus additional filter

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. 

View solution in original post

Community Support Team
Community Support Team

Re: Calculating data for the day before the selected plus additional filter

@guineapig 

 

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

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 331 members 3,040 guests
Please welcome our newest community members: