Forum Discussion
Solved
Extrapolating "Rent amount" from start & end date & amount
Hey, first post in this community! Saw a lot of great helps on the forum, but haven't seen something like this.
Currently have data on Lease ID (contract) and Customer ID (customer), with start/end contract date and Rent amount as below:
Lease ID | Customer ID | Start | End | Rent amount |
| 1 | 1 | 1/1/2022 | 12/31/2022 | 1000 |
| 2 | 1 | 1/1/2023 | 12/31/2023 | 1100 |
| 3 | 2 | 7/1/2022 | 12/31/2025 | 2000 |
| 4 | 3 | 7/1/2022 | 6/30/2023 | 1500 |
| 5 | 3 | 7/1/2023 | 6/30/2024 | 1600 |
Is there any way for Power BI/Query to project the next 24 months for these leases? Below is sample output:
| Customer | 1/1/2022 | 2/1/2022 | 3/1/2022 | 4/1/2022 | 5/1/2022 | 6/1/2022 | 7/1/2022 | 8/1/2022 | 9/1/2022 | 10/1/2022 | 11/1/2022 | 12/1/2022 | 1/1/2023 | 2/1/2023 | 3/1/2023 | 4/1/2023 | 5/1/2023 | 6/1/2023 | 7/1/2023 | 8/1/2023 | 9/1/2023 | 10/1/2023 | 11/1/2023 | 12/1/2023 |
| 1 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1100 | 1100 | 1100 | 1100 | 1100 | 1100 | 1100 | 1100 | 1100 | 1100 | 1100 | 1100 |
| 2 | 0 | 0 | 0 | 0 | 0 | 0 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 | 2000 |
| 3 | 0 | 0 | 0 | 0 | 0 | 0 | 1500 | 1500 | 1500 | 1500 | 1500 | 1500 | 1500 | 1500 | 1500 | 1500 | 1500 | 1500 | 1600 | 1600 | 1600 | 1600 | 1600 | 1600 |
Thanks for the help!
See attached for a solution proposal that uses only Power Query, a crossjoin, and calculated columns.
2 Replies
- lbendlin
Super User
See attached for a solution proposal that uses only Power Query, a crossjoin, and calculated columns.
This is great! Thank you for helping out
