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.
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.
Amazing stuff johnyip !!
I have a few more additional questions. Would it be better to start a new thread? Do you accept tips? 🙂
what tips?