Hello,
I am currently writing a thesis for my university in cooperation with a media-company. I built a real estate database in MS-Access. I try to create dashboards based on this database, but the numbers created are wrong, because Access does not unerstand that a contract runs for a period of time.
Example:
Imagine 2 lease contracts.
The first one runs from 1st of April 2016 to the end of 2017. Monthly lease expense 2000 USD
The second one runs from the beginning of 2013 until the end of 2020. Monthly lease expense 2020 USD
In my table I have the attributes:
ContractID (primary key) (auto number)
LeaseFrom (date)
LeaseUntil (date)
MonthlyLeaseExp (currency)
So I have the data:
1 01.04.2016 31.12.2017 2000
2 01.01.2013 31.12.2020 2000
When I now go to Power BI, a Microsoft provided service for building dashboards, and I set filters:
Lease from = is 2016
Lease until = is smaller than 2017
in order to show the lease contracts in 2016 with their montly lease expense.
The answer I get is:
Lease contract 1 = 2000 USD
But lease contract 2 is not shown. I guess its because Access only sees 2 points in time. In case number 2, 2013 and 2020, which is not 2016. However logically this lease contract is valid in 2016 and I want this to be shown.
What do I have to do to model a time period? I would be really thankful for answers, ideas etc.
Kind regards
Jannik