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 display data for the most recent date 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:
- Summing the number of units is blatantly wrong. The number of units at a single property is generally static.
- 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: Display Data for the Most Recent Date 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 display data for the most recent date when the user selects a date range in Power BI.
For more tips, be sure to check out our other blog posts. We’re always happy to help!