Help calculating the closest less than date

[anin]

Hello,

I am new to PowerBI and I am struggling with filtering data closest to a selected day.

My data table looks like this:

ID    Date             Value

1      1/2-2020      a

1      1/5-2020      b

1      1/8-2020      c

2      1/2-2020      x

2      1/5-2020      y

2      1/8-2020      z

I essentially want to select two dates to compare the values of the entries closest less than date.

So for example I want to be able chose the dates 1/3-2020 and 1/9-2020 in a slicer and get the following outcome:

ID     Value1     Value2

1       a              c

2       x              z

Kind regards

Anders

[v-deddai1-msft · Accepted solution]

Hi @anin ,

Based on your sample data, you need to create two date table for two slicers:

Datetable1 = CALENDAR(DATE(2020,1,1),DATE(2020,1,31)) 

Datetable2 = Datetable1 

Then create two measures for value1 and value2:

Value1 = var a = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Date]<=MAX(Datetable1[Date]))) return CALCULATE(MAX('Table'[Value]),'Table'[Date] = a)

Value2 = var a = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Date]<=MAX(Datetable2[Date]))) return CALCULATE(MAX('Table'[Value]),'Table'[Date] = a)

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdnzOF02owBEjhNCEd...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[anin]

Hello @amitchandak ,

Thank you for your response, however I am trouble getting it to work, as I am not getting any results in my table.

Can you please take a look at these screenshots from my PowerBI file, and help me identify what is going wrong?

Kind regards

Anders

[amitchandak]

@anin , Use an independent date table for the slicer , and then try these formula's

Value1 =
var _min = minx(allselected(Date), Date[Date])
var _max = maxx(filter(allselected(Date), Date[Date] <=_min),Date[Date] )
return
calculate(sum(Table[Value]), filter(Table, Table[date] =_max))

Value2 =
var _min = maxx(allselected(Date), Date[Date])
var _max = maxx(filter(allselected(Date), Date[Date] <=_min),Date[Date] )
return
calculate(sum(Table[Value]), filter(Table, Table[date] =_max))