Pfthb

Object: Sum up the nearest date's value by a given date

Here is my data

Table: MyData
+-------------------------------+
| ID TradeDate Value |
+-------------------------------+
| 1 2018/11/30 105 |
| 1 2018/11/8 101 |
| 1 2018/10/31 100 |
| 1 2018/9/30 100 |
| 2 2018/11/30 200 |
| 2 2018/10/31 201 |
| 2 2018/9/30 205 |
| 3 2018/11/30 300 |
| 3 2018/10/31 305 |
| 3 2018/9/30 301 |
+-------------------------------+

I create a table named 'DateList' and use slicer to select a specified date

DateList Slicer

I want to achieve the result as follows:

as of *11/9/2018*
+-----------------------------------+
| ID TradeDate Value |
+-----------------------------------+
| 1 2018/11/8 101 |
| 2 2018/10/31 201 |
| 3 2018/10/31 305 |
+-----------------------------------+
| Total 607 |
+-----------------------------------+

Currently, I try to use the steps to achieve the above result.

First, i want to find the nearest date from table 'MyData' use the new measure

MyMaxDate = CALCULATE(MAX(MyData[TradeDate]),Filter(MyData, MyData[TradeDate] <= FIRSTDATE(DateList[Date]) ))

Second, i create a new measure "MySum" to the sum up the values if [tradedate] equal to the "MyMaxDate"

MySum = CALCULATE(SUM(MyDate[Value]),Filter(MyData, MyData[TradeDate] = MyMaxDate))

Third, i create a matrix to show the result (see Result)

Unfortunately, the result 1313 is different from my goal 607
So, how can i fix my DAX formula to achieve the right result?

Many Thanks

You can calculate the closest date by taking a min over the difference in dates and then taking the minimal date with that minimal difference.

MyDate = 
VAR SlicerDate = MIN(DateList[Date])
VAR MinDiff =
 MINX(
 FILTER(ALL(MyData),
 MyData[ID] IN VALUES(MyData[ID])
 ),
 ABS(SlicerDate - MyData[TradeDate]))
RETURN
 MINX(
 FILTER(ALL(MyData),
 MyData[ID] IN VALUES(MyData[ID])
 && ABS(SlicerDate - MyData[TradeDate]) = MinDiff
 ),
 MyData[TradeDate])

From there you can create the summing measure fairly easily:

MySum = CALCULATE(SUM(MyData[Value]), FILTER(MyData, MyData[TradeDate] = [MyDate]))