Forum Discussion
Rolling Total between Contract Dates
Hello,
I'm looking for a solution to calculate a running total with a few wrinkles.
Total Area of the facility = 4,500
Tenant 😧 When a contract expires, the formula in the backing data automatically sets the "Total Area" for the tenant (Tenant D) to zero, and will populate the "Expired Area" cell.
The wrinkle is that when calculating a running total, this initial 500 should be counted during lease start, but subtracted out when the lease ends.
| Tenant | Lease Start | Lease End | Total Area | Expired Area |
| A | 1/1/2001 | 12/31/2006 | 1,000 | |
| B | 3/1/2001 | 12/1/2007 | 1,000 | |
| C | 6/1/2001 | 12/1/2008 | 1,000 | |
| D | 8/1/2001 | 9/1/2001 | 500 | |
| E | 9/1/2001 | 12/1/2008 | 1,000 | |
| F | 10/1/2001 | 12/1/2008 | 500 |
butabara808 Sorry, I missed an important part in my DAX.
In Step 3, please use the following instead.
Running Area = CALCULATE(SUM('Table'[Area]),FILTER(ALLSELECTED('Table'),MAX('Date'[Date])>='Table'[Lease Start]))- CALCULATE(SUM('Table'[Expired Area]),FILTER(ALLSELECTED('Table'),'Table'[Lease End]<=MAX('Date'[Date])))* Also editted the original post for easy referecne.
7 Replies
Not sure if "the formula in the backing data" is referring to a DAX measure, or the product of ETL process.
If the backing data is prepared during ETL and your data will be refreshed promptly, you can simply sum all the total area (1000+1000+1000+1000+500=4500)Apologies for the imprecise words. The backing data refers to an excel sheet and an excel formula.
Ideally, I'd like to show an 'occupancy over time' line chart, so that initial 500 would need to be counted during 'Lease Start' and subsequently subtracted upon 'Lease End'
Hi butabara808 ,
To build your line chart, please follow the below steps.
1. Build a calculated table 'Date' using the following DAX.
Date = CALENDAR(IF(MIN('Table'[Lease Start])<MIN('Table'[Lease End]),MIN('Table'[Lease Start]),MIN('Table'[Lease End])), IF(MAX('Table'[Lease Start])>MAX('Table'[Lease End]),MAX('Table'[Lease Start]),MAX('Table'[Lease End])))2. Build a calculated column [Area] as the following definition.
Area = 'Table'[Expired Area] + 'Table'[Total Area]3. Build a measure [Running Area] as the following definition.
Running Area = CALCULATE(SUM('Table'[Area]),FILTER(ALLSELECTED('Table'),MAX('Date'[Date])>='Table'[Lease Start]))- CALCULATE(SUM('Table'[Expired Area]),FILTER(ALLSELECTED('Table'),'Table'[Lease End]<=MAX('Date'[Date])))4. Build your line chart as follows.
