Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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. 

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      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 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

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