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          ...
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. 
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 , 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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.