Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dpc_development
Helper III
Helper III

Optimisation help: Calculating daily revenue based on monthly rates and effective period

Please see the following for a simplified version of my data model. I can change the source tables, if required, in production.

 

https://drive.google.com/file/d/1YC4_80hu_BlL7-SriyQQa97lrCyEY5VN/view?usp=sharing

 

This is for a real estate business. I have a list of properties with units, and each unit can have many leases with an effective start and end date where a certain monthly rent is charged. The fact table shows when these leases started or ended and how much was the rent.

 

When visualising weekly, monthly, quarterly, I need to calculate how much is the average rent per unit and already have a calculated measure within the file.

 

However, I use SUMX and I am afraid the calculation performance will really detoriorate when adding more properties and countries, as a majority of the calculation is on the Formula Engine.

 

One alternative would be to precalculate the values in the fact table on a daily basis and do a simple SUM. This may lead to a different performance issue where the number of rows really blows up as there can be 100s of thousands of units and having one row per date for many years can get really big.

 

Does anyone have experience calculating revenue for a period based on from-to ranges of monthly rates? I'd appreciate any suggestions optimising my DAX formula. Originally, the Monthly Rent value was in the Dimension table and there was not 'End Date' column. I added these in later to help with my formula.

2 REPLIES 2
dpc_development
Helper III
Helper III

Hi @daxer-almighty good catch on the first datediff, I'll fix that immediately. On the second datediff, I actually need to find overlapping days, and that was the best way I could think of, and need to find an optimal solution.

Example: If Calculation date is 1st to 31st and Lease is from 10th to 20th, then overlapping day count is 10.

 

Regarding expanding the ranges into a per day format, we tried that and the smallest country has 3mn rows, and we plan to merge at least 7-8 countries together. So that may also be an issue long term.

 

It's a trade off between writing fewer rows to the fact table or calculating more number of rows in a simpler manner.

daxer-almighty
Solution Sage
Solution Sage

Hi @dpc_development 

 

First, you should take out the divisor

 

                --divide by total possible number of days in calculation range
                / (DATEDIFF(dt_from, dt_to, DAY) + 1)

 

from under SUMX as it's the same for all summands. This way you'll only be summing up integers - much, much faster. You can divide after you've performed the summation. 

 

Second, you are using DATEDIFF under SUMX. This means Formula Engine will have to work hard. You have to move the calculation into Storage Engine. How to do that? You have to (at least try) expand the ranges into individual days. It means your fact table will be longer (maybe even much longer) but for PBI it's not a problem and it's much better for DAX. Then, instead of DATEDIFF you'll be able to just filter the days between the range and do COUNTROWS. This may be pushed down to the SE (most likely it will be). Also, you should SUMX over Monthly Rents, not over the rows of the fact table. Do it and you'll have a very fast formula.

 

This is the best you can do with the current model.

 

By the way, pre-calculating anything here is of no use since the formula depends on a selection of dates from the Calendar. Unless, you've got some favorite ranges that'll be used 95% of the time.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.