Contract income by year - depend on contract length

0
votes

First of all, I don't have much experience with Power BI and DAX.

I have question about calculating yearly income depend contract length and showing company year by year income.

Data is stored in Excel file and all contracts have information about contract_start (datetime), contract_end (datetime) and annual contract value (income).

So for example:

I have 5 contracts (A, B, C, D, E):

  • A - end year 2019 (worth 1M/per year €)
  • B - end year 2019 (worth 1M/per year €)
  • C - end year 2020 (worth 2M/per year €)
  • D - end year 2021 (worth 1M/per year €)
  • E - end year 2022 (worth 5M/per year €)

So my end result must be (sum of annual contract income per year):

enter image description here

  • 2019 = A+B+C+D+E
  • 2020 = C+D+E
  • 2021 = D+E
  • 2022 = E

Edit: Additional: It's possible to also include contracts, which have blank contract_end? If contract has blank contract_end it means that contract is valid until cancelation.

So I have beside contract A, B, C, D, E also 2 contract, F and G which have "blank" contract_end and each it's worth 1M/year.

  • A - end year 2019 (worth 1M/per year €)
  • B - end year 2019 (worth 1M/per year €)
  • C - end year 2020 (worth 2M/per year €)
  • D - end year 2021 (worth 1M/per year €)
  • E - end year 2022 (worth 5M/per year €)
  • F - until cancelation (worth 1M/per year €)
  • G - until cancelation (worth 1M/per year €)

It's possible to add "virtual" 2M€ to each year in chart (sum of F and G)?

Example in image: enter image description here

1
powerbidax

1 Answers

2
votes

If your table looks something like this:

enter image description here

then a maesure like this:

Total_Income =
CALCULATE (
    SUM ( 'table'[Yearly_Income] ),
    FILTER (
        ALL ( 'table' ),
        'table'[EndYear] >= SELECTEDVALUE ( 'table'[EndYear] )
    )
)

results in a Column chart like this ('table'[Endyear] on Axis and [Total_Income] on Value):

enter image description here