Forum Discussion
Rolling Total between Contract Dates
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.
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.
Thank you for your suggestion. I tried the steps above, but it isn't quite working.
In your image example above, the runnign total should never increase over 4,500 since the maximum occupancy is 4,500.
I tried on my own data set and ended up with some abnormal results.
This example shows occupancy at 55k plus in 2010, even though the first lease doesn't start until 2022. Also, I'm not sure what causes the spike up again after 2035, as the data set does not contain any information beyond that date.
Ideally, would like to be able to visually see the occupancy ramp up as leases start, and occupancy drop as leases end.
Thank you for your help
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.