Forum Discussion

anin

Frequent Visitor

5 years ago

Solved

Help calculating the closest less than date

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

Need Help

v-deddai1-msft
5 years ago
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/EdnzOF02owBEjhNCEdC9GrgBsx0oilgHLeCLvRJ7ojxOrw?e=04NNl4

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

3 Replies

v-deddai1-msft
Community Support
5 years ago
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/EdnzOF02owBEjhNCEdC9GrgBsx0oilgHLeCLvRJ7ojxOrw?e=04NNl4

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
amitchandak
Super User
5 years ago
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))
anin
Frequent Visitor
5 years ago
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