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'm trying to build a report that shows values relative to the current date for a variety of properties in a table.
Property Name | Pre Lease % | Occupied % | Web Traffic |
Site A | 55% | 25% | 37 |
Site B | 65% | 20% | 53 |
Site C | 75% | 15% | 48 |
Example: If I set the Date Slicer to 3 months ago, I want it to show me the data that was entered nearest to February 6th (3 months ago). However, it seems I only have the ability to pull the average, sum, etc of the values from that range. These values are entered in to our database weekly, so if I set it to "Do Not Summarize", I get a ton of Site A, Site B, and Site C lines, each with their own entry.
Thank you for your help and I'm happy to provide more context if necessary!
Hi @bkirshenbaum ,
Could you please post some simple sample data and your desired result to have a test if possible? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
Daniel He
Sorry about that!
Below is the sample data, and I am trying to filter based upon the Modified Date. There are several entries for each property throughout time, and when we filter via the slicer, we want to be able to capture a single piece of data that is closest to the relative date selected and display it in a table or matrix.
Thank you!
Property ID:Property Name | Occupied | calcOccupied | calcPreleased | Pre Leased | Weekly Traffic | Competition Rank | Modified |
identity | 0 | 0 | 0 | 0 | 0 | 0 | Monday, January 22, 2018 |
tower | 100 | 1 | 0.18 | 18 | 0 | 1 | Monday, January 22, 2018 |
4th street | 0 | 0 | 0 | 0 | 0 | 2 | Monday, January 22, 2018 |
advenir | 90 | 0.9 | 0 | 0 | 0 | 3 | Monday, January 22, 2018 |
advenir | 90 | 0.9 | 0 | 0 | 4 | 3 | Monday, January 22, 2018 |
4th street | 98 | 0.98 | 0 | 0 | 0 | 2 | Monday, January 22, 2018 |
tower | 100 | 1 | 0.18 | 18 | 0 | 1 | Monday, January 22, 2018 |
tower | 99.63 | 0.9963 | 0.2232 | 22.32 | 4 | 1 | Monday, February 12, 2018 |
4th street | 98 | 0.98 | 0.3 | 30 | 0 | 2 | Monday, February 12, 2018 |
advenir | 90 | 0.9 | 0 | 0 | 4 | 3 | Monday, February 12, 2018 |
advenir | 90 | 0.9 | 0 | 0 | 4 | 3 | Monday, February 12, 2018 |
advenir | 0 | 0 | 0 | 0 | 0 | 3 | Monday, February 12, 2018 |
tower | 100 | 1 | 0.3 | 30 | 7 | 1 | Thursday, March 1, 2018 |
4th street | 97 | 0.97 | 0.35 | 35 | 4 | 2 | Thursday, March 1, 2018 |
advenir | 0 | 0 | 0 | 0 | 0 | 3 | Thursday, March 1, 2018 |
4th street | 98 | 0.98 | 0.3 | 30 | 0 | 2 | Thursday, March 8, 2018 |
advenir | 0 | 0 | 0 | 0 | 0 | 3 | Thursday, March 8, 2018 |
advenir | 0 | 0 | 0 | 0 | 0 | 3 | Thursday, March 8, 2018 |
tower | 100 | 1 | 0.3 | 30 | 7 | 1 | Thursday, March 8, 2018 |
4th street | 0 | 0 | Friday, March 16, 2018 | ||||
4th street | 98 | 0.98 | 0.32 | 32 | 5 | 2 | Friday, March 16, 2018 |
4th street | 98 | 0.98 | 0.5142 | 51.42 | 12 | 2 | Friday, March 23, 2018 |
4th street | 97 | 0.97 | 0.5516 | 55.16 | 21 | 2 | Friday, March 30, 2018 |
4th street | 97 | 0.97 | 0.57 | 57 | 21 | 2 | Friday, April 6, 2018 |
tower | 100 | 1 | 0.6 | 60 | 25 | 1 | Friday, June 22, 2018 |
advenir | 81 | 0.81 | 0.87 | 87 | 0 | 3 | Friday, June 22, 2018 |
4th street | 95 | 0.95 | 0.9 | 90 | 43 | 2 | Tuesday, June 26, 2018 |
4th street | 95 | 0.95 | 0.9 | 90 | 43 | 2 | Tuesday, July 10, 2018 |
4th street | 95 | 0.95 | 0.9 | 90 | 43 | 2 | Tuesday, July 10, 2018 |
tower | 100 | 1 | 0.6 | 60 | 25 | 1 | Tuesday, July 10, 2018 |
tower | 100 | 1 | 0.6 | 60 | 25 | 1 | Tuesday, July 10, 2018 |
tower | 100 | 1 | 0.75 | 75 | 12 | 1 | Thursday, July 19, 2018 |
4th street | 95 | 0.95 | 0.94 | 94 | 28 | 2 | Thursday, July 19, 2018 |
advenir | 86 | 0.86 | 0.88 | 88 | 0 | 3 | Thursday, July 19, 2018 |
tower | 100 | 1 | 0.7 | 70 | 12 | 1 | Thursday, July 19, 2018 |
fountain | 0 | 0 | 0 | 0 | 0 | 0 | Monday, July 23, 2018 |
tower | 100 | 1 | 0.9 | 90 | 20 | 1 | Friday, July 27, 2018 |
advenir | 89 | 0.89 | 0.91 | 91 | 9 | 3 | Friday, July 27, 2018 |
fountain | 0 | 0 | 0 | 0 | 0 | 0 | Friday, July 27, 2018 |
identity | 0 | 0 | 0 | 0 | 0 | 0 | Thursday, September 13, 2018 |
tower | 100 | 1 | 0.9 | 90 | 20 | 1 | Thursday, September 13, 2018 |
4th street | 95 | 0.95 | 0.98 | 98 | 28 | 2 | Thursday, September 13, 2018 |
4th street | 98 | 0.98 | 0 | 0 | 28 | 2 | Thursday, September 13, 2018 |
advenir | 89 | 0.89 | 0.91 | 91 | 9 | 3 | Thursday, September 13, 2018 |
advenir | 89 | 0.89 | 0.91 | 91 | 9 | 3 | Thursday, September 13, 2018 |
fountain | 0 | 0 | 0 | 0 | 0 | 0 | Thursday, September 13, 2018 |
fountain | 96 | 0.96 | 0 | 0 | 15 | 3 | Thursday, September 13, 2018 |
fountain | 96 | 0.96 | 0 | 0 | 15 | 3 | Thursday, September 13, 2018 |
tower | 100 | 1 | 0 | 0 | 10 | 1 | Thursday, September 13, 2018 |
identity | 0 | 0 | 0 | 0 | 0 | 0 | Thursday, September 20, 2018 |
tower | 100 | 1 | 0 | 0 | 10 | 1 | Thursday, September 20, 2018 |
tower | 100 | 1 | 0 | 0 | 0 | 1 | Thursday, September 20, 2018 |
4th street | 97 | 0.97 | 0.03 | 3 | 8 | 2 | Thursday, September 20, 2018 |
advenir | 85 | 0.85 | 0 | 0 | 9 | 3 | Thursday, September 20, 2018 |
fountain | 96 | 0.96 | 0 | 0 | 15 | 3 | Thursday, September 20, 2018 |
fountain | 96 | 0.96 | 0 | 0 | 9 | 3 | Thursday, September 20, 2018 |
identity | 0 | 0 | 0 | 0 | 0 | 0 | Thursday, September 27, 2018 |
tower | 100 | 1 | 0 | 0 | 12 | 2 | Thursday, September 27, 2018 |
4th street | 97 | 0.97 | 0.03 | 3 | 8 | 1 | Thursday, September 27, 2018 |
advenir | 85 | 0.85 | 0 | 0 | 8 | 3 | Thursday, September 27, 2018 |
fountain | 96 | 0.96 | 0 | 0 | 9 | 3 | Thursday, September 27, 2018 |
fountain | 96 | 0.96 | 0 | 0 | 9 | 3 | Thursday, September 27, 2018 |
fountain | 96 | 0.96 | 0 | 0 | 11 | 4 | Thursday, September 27, 2018 |
4th street | 98 | 0.98 | 0.05 | 5 | 8 | 1 | Thursday, September 27, 2018 |
identity | 0 | 0 | 0 | 0 | 0 | 0 | Thursday, October 4, 2018 |
tower | 100 | 1 | 0 | 0 | 12 | 2 | Thursday, October 4, 2018 |
4th street | 97 | 0.97 | 0.01 | 1 | 7 | 1 | Thursday, October 4, 2018 |
fountain | 0 | 0 | Thursday, October 4, 2018 | ||||
fountain | 95.61 | 0.9561 | 0 | 0 | 11 | 4 | Thursday, October 4, 2018 |
advenir | 85 | 0.85 | 0 | 0 | 6 | 3 | Thursday, October 4, 2018 |
tower | 100 | 1 | 0 | 0 | 5 | 2 | Thursday, October 4, 2018 |
identity | 0 | 0 | 0.000016 | 0.0016 | 0 | 0 | Thursday, October 11, 2018 |
tower | 100 | 1 | 0 | 0 | 0 | 2 | Thursday, October 11, 2018 |
4th street | 98 | 0.98 | 0.01 | 1 | 6 | 1 | Thursday, October 11, 2018 |
advenir | 85 | 0.85 | 0 | 0 | 6 | 3 | Thursday, October 11, 2018 |
advenir | 85 | 0.85 | 0 | 0 | 3 | 3 | Thursday, October 11, 2018 |
fountain | 95.61 | 0.9561 | 0 | 0 | 11 | 4 | Thursday, October 11, 2018 |
fountain | 95.61 | 0.9561 | 0 | 0 | 6 | 4 | Thursday, October 11, 2018 |
advenir | 85 | 0.85 | 0 | 0 | 5 | 3 | Thursday, October 18, 2018 |
fountain | 95.61 | 0.9561 | 0 | 0 | 5 | 4 | Thursday, October 18, 2018 |
identity | 0 | 0 | 0.000016 | 0.0016 | 0 | 0 | Thursday, October 18, 2018 |
4th street | 98 | 0.98 | 0.01 | 1 | 3 | 1 | Thursday, October 18, 2018 |
tower | 100 | 1 | 0 | 0 | 0 | 2 | Thursday, October 18, 2018 |
tower | 100 | 1 | 0 | 0 | 0 | 2 | Thursday, October 18, 2018 |
tower | 99 | 0.99 | 0.0221 | 2.21 | 5 | 2 | Thursday, October 18, 2018 |
identity | 0 | 0 | 0.000016 | 0.0016 | 0 | 0 | Thursday, October 25, 2018 |
advenir | 0 | 0 | Thursday, October 25, 2018 | ||||
advenir | 83.44 | 0.8344 | 0 | 0 | 8 | 3 | Thursday, October 25, 2018 |
fountain | 95.61 | 0.9561 | 0 | 0 | 5 | 4 | Thursday, October 25, 2018 |
fountain | 95.61 | 0.9561 | 0 | 0 | 5 | 4 | Thursday, October 25, 2018 |
tower | 99 | 0.99 | 0.03 | 3 | 5 | 2 | Thursday, October 25, 2018 |
4th street | 99 | 0.99 | 0.0409 | 4.09 | 4 | 1 | Thursday, October 25, 2018 |
advenir | 83.44 | 0.8344 | 0 | 0 | 8 | 3 | Thursday, October 25, 2018 |
fountain | 97 | 0.97 | 0 | 0 | 3 | 4 | Thursday, October 25, 2018 |
identity | 0 | 0 | 0.0032 | 0.32 | 0 | 0 | Thursday, November 1, 2018 |
4th street | 99 | 0.99 | 0.074 | 7.4 | 4 | 1 | Thursday, November 1, 2018 |
advenir | 82.7 | 0.827 | 0 | 0 | 5 | 3 | Thursday, November 1, 2018 |
fountain | 97 | 0.97 | 0 | 0 | 3 | 4 | Thursday, November 1, 2018 |
fountain | 97 | 0.97 | 0 | 0 | 3 | 4 | Thursday, November 1, 2018 |
fountain | 97 | 0.97 | 0 | 0 | 2 | 4 | Thursday, November 1, 2018 |
tower | 99 | 0.99 | 0.03 | 3 | 6 | 2 | Thursday, November 1, 2018 |
tower | 99 | 0.99 | 0.035 | 3.5 | 6 | 2 | Thursday, November 1, 2018 |
identity | 0 | 0 | 0.0064 | 0.64 | 0 | 0 | Thursday, November 8, 2018 |
advenir | 82.7 | 0.827 | 0 | 0 | 2 | 3 | Thursday, November 8, 2018 |
fountain | 95 | 0.95 | 0 | 0 | 7 | 4 | Thursday, November 8, 2018 |
4th street | 99 | 0.99 | 0.1 | 10 | 8 | 1 | Thursday, November 8, 2018 |
tower | 98.9 | 0.989 | 0.0775 | 7.75 | 5 | 2 | Thursday, November 8, 2018 |
identity | 0 | 0 | 0.0097 | 0.97 | 0 | 0 | Thursday, November 15, 2018 |
advenir | 82.78 | 0.8278 | 0 | 0 | 4 | 3 | Thursday, November 15, 2018 |
tower | 99 | 0.99 | 0.1 | 10 | 5 | 2 | Thursday, November 15, 2018 |
advenir | 82.78 | 0.8278 | 0 | 0 | 4 | 3 | Thursday, November 15, 2018 |
4th street | 99 | 0.99 | 0.1 | 10 | 8 | 1 | Thursday, November 15, 2018 |
fountain | 95 | 0.95 | 0 | 0 | 7 | 4 | Thursday, November 15, 2018 |
4th street | 99 | 0.99 | 0.1 | 10 | 8 | 1 | Wednesday, November 21, 2018 |
tower | 99 | 0.99 | 0.1 | 10 | 5 | 2 | Wednesday, November 21, 2018 |
tower | 99 | 0.99 | 0.1 | 10 | 5 | 2 | Wednesday, November 21, 2018 |
identity | 0 | 0 | 0.0097 | 0.97 | 0 | 0 | Wednesday, November 21, 2018 |
identity | 0 | 0 | 0.0097 | 0.97 | 0 | 0 | Wednesday, November 21, 2018 |
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |