Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
@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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |