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