Linkedin Link
CONTACT

Category: Data Analytics

Categories
Data Analytics

Show Most Recent Data When User Selects A Date Range in Power BI

If you build Power BI dashboards for real estate firms, then you have likely visualized data over time. And quite often, you will display tabular data below line charts or similar time series visuals. You may choose to have the data table represent totals or averages for the time period selected. However, it often doesn’t make sense to sum or average the data. In these scenarios, you can add a date column to the table and show each row of dated data. This results in a cluttered data table that may confuse your users. A better option is to show the most recent data when the user selects a date range in Power BI. Here’s how. 

Let's Review the Problem: Multiple Rows for Each Date in a Date Range

Let’s walk through an example of the problem. In our scenario, the user wants to see collections trends in line charts along the top of your report. Examples include the collections rate, total receivables, number of delinquencies, among others. The user also wants to see collections, receivables, and delinquencies in a table. Let’s go to Power BI Desktop to see an example.

In this scenario, the number of units, delinquencies, and receivables sum for the time period selected. The collections rate and % of monthly charges are essentially averages. Two main issues to highlight:

  1. Summing the number of units is blatantly wrong. The number of units at a single property is generally static.
  2. Summing receivables data also does not make sense. Receivables are an asset on your balance sheet, which means that they should only be represented for a point in time.

While users may want to see the sums of delinquencies or average collections rates, you will be better off displaying a single date of data for the entire row. 

Solution: Show Most Recent Data When User Selects a Date Range in Power BI

How can you fix the issues above? Simply create a new measure for each column using the LASTNONBLANKVALUE function. Here’s how that function works when you want to calculate the number of units for the latest date selected:

				
					LatestNumberOfUnits = LASTNONBLANKVALUE(DimDate[ActualDate],[NumberOfUnits])

According to Microsoft’s explanation, LASTNONBLANKVALUE calculates the last non-blank value of an <expression> corresponding to the sorted values of a <column>.

				
					LASTNONBLANKVALUE(<column>, <expression>)

And that’s exactly what we’re doing in the above code for the LatestNumberOfUnits. We told Power BI DAX to look through the DimDate[ActualDate] column, which is the primary date column in our data table. Then, tell us the [NumberOfUnits] for the latest date where [NumberOfUnits] was not blank. 

Requirements and Notes

Now, one hiccup you could envision is that DAX pulls the last non-blank value with varying dates for each measure. If you don’t have consistent data storage processes, then you will have inconsistent latest dates of data.  

Also, you may read other content that suggests you calculate the maximum date for the date range selected. This does not work if you have non-sequential data. Most real estate firms store data on a weekly or monthly basis, rarely daily. As such, the LASTNONBLANKVALUE function will be your best bet. You will also need to use a date table in your data model for this formula to work appropriately.

Conclusion

That’s it! Now you know how to show most recent data when the user selects a date range in Power BI. 

Want more? Subscribe below.

MY COMMITMENT | Never sell or share your data | Provide useful and impactful stories

Categories
Data Analytics real estate Startup Story

Build vs. Buy in Proptech Reporting

Reporting got you feeling overwhelmed? I’ve been there. Should you buy a software package and try to make your reporting fit in that box? Or go with a vendor that can customize for you? Or should you choose the most flexible (albeit expensive and time-consuming) solution and build it yourself?! Let’s weigh the build vs. buy proptech for enhancing your reporting game.

First... Do You *Really* Need Something Else?

On second thought, is your current system working for your size firm? If you’re a small team of 10 or less employees, stick with the out-of-the-box property management software reports. These bad boys are powerful enough to get the job done for daily operations and accounting. Plus, you’ll have the latest data at your fingertips without any extra effort.

When to Build

Next, when should you build? Simple answer is when your reporting needs aren’t met by your existing software package’s pre-made reports. If you have a team who can code in SQL, then you’ll be off to the races. For example, you can leverage Yardi’s custom SQL function for those specific requests like owner reports or a weekly executive dashboard. Keep in mind, you or your team will not only need strong SQL skills, you’ll also need to know the software application’s data structure. 

Or, if you have someone on your team who knows Power BI, DOMO, or similar, you can pull in your data into one of those tools. Again, you’ll need to know the software application’s data structure. 

When to Buy

Lastly, when should you buy? Easy answer is when it’s more cost effective to do so. For reporting specifically, you may decide that you need a ton of custom reports and that having a consultant build all of them is more expensive than someone in-house using Power BI. Or you decided that you want better visuals and trend tracking. 

Don’t break the bank if you don’t need to! Use an existing SaaS product either for your data integration, visualization, or both. These platforms offer easy integration with various property types and can meet most analytics needs in just a few weeks. Plus, they’re way cheaper than building reports in-house. 

Conclusion

In the end, knowing whether to build vs. buy for your proptech reporting is all about your needs and budget. Got more questions? Reach out to me for guidance.

Want more? Subscribe below.

MY COMMITMENT | Never sell or share your data | Provide useful and impactful stories

Categories
Data Analytics real estate Startup Story

How to Create a Proptech Strategy

Congrats on your new gig leading tech for a real estate firm! Ready to make some magic happen? Here are 6 steps to help you create a killer proptech strategy.

Step 1: Get Your Execs on Board

Leaders set the tone, so it’s crucial to have at least one C-level exec championing your proptech plans. The pandemic showed that businesses need to adapt and embrace new ways of doing things, so this should be an easy sell. Plus, you can highlight that tech-savvy employees are more productive (and presumably happier since they automated boring tasks!).

Step 2: Create a Proptech Strategy Team

Assemble a small, enthusiastic team to drive your proptech initiatives. Ideally, team members’ roles should benefit the most from technology and automation. Don’t let the group get too big – if it takes more than two pizzas to feed everyone, you’re doing it wrong!

Step 3: Identify Your Needs

Figure out what you want to accomplish with technology. Start with small groups to identify areas for improvement, rank the pain points in order of priority, and estimate timelines. Keep in mind that not all problems require tech solutions.

Step 4: Determine Your Budget

Once you know what you need, you should create a budget. It helps to identify opportunities and cost savings and to make your case for executive buy-in.

Step 5: Find Potential Solutions

There are tons of proptech vendors out there, so do your research. Start by working with proptech venture capital groups, but also reach out to peer firms to learn about their experiences. Collaboration benefits everyone.

Step 6: Track Your Progress

Measure your success to see if you achieved your goals. Identify the ideal outcomes for each proptech solution, track progress, and hold your team accountable. Don’t forget to assign team members to key projects to boost engagement.

Conclusion

And that’s it! Follow these 6 steps and you’ll be on your way to create an awesome proptech strategy. Don’t forget to check out our other blog posts for more tips!

Want more? Subscribe below.

MY COMMITMENT | Never sell or share your data | Provide useful and impactful stories