Show cumulative value next 10 years from selected year

[MarianneElver]

Hi, 

I have been struggling with this for a while, and have searched in vain!
I am looking for creating af running total of investments, where the start year has to be selected in a slicer, and then show running total from selected start year and 10 years forward.

My input table "Investment" table is like this:

Year	Dates	Investment
2024	01-01-2024	500
2025	01-01-2025	500
...	...	...
2040	01-01-2040	500

That is, the same investment of 500 each year, spanning from 2024 to 2040. 
The goal is to limit the timespan from a selected year, and then 10 years forward.

I have tried various combinations with dates, both date columns in the Investment, date columns with the connected CALENDAR table, and with a disconnected table, dim year. 

Current status is that I get the running total from 2024 to 2040, and I would like to limit that timespan to a selection.
Here is my measure:

Investment = 
VAR SelectedYear = SELECTEDVALUE('dim year'[Year]) //disconnected slicer

VAR CumulativeValue =

    CALCULATE(

        SUM('Investment'[Investment]),

             'dim CALENDAR'[Dates] >= SelectedYear &&

             'dim CALENDAR'[Dates] <= MAX('Investment'[Dates]),

              ALL('dim CALENDAR'[Dates])

        )

   RETURN CumulativeValue

Any help is highly appreciated!

KR Marianne

[lbendlin]

You seem to assume there's a starting year?

[MarianneElver]

Yes, the starting year is to be selected in a slicer. Either connected or disconnected, both are possible!

[lbendlin]

Then the result will always be 5000.

[MarianneElver]

Totally, the result will always be 5000. But each year, the sum is 500 + the sum of the previous year. 

The challenge is to be able to select which year, the investments start, and how the cumulative value unfolds over the following 9 years.

[MarianneElver]

So I guess we are back to this calculation!

Investment = 
VAR SelectedYear = SELECTEDVALUE('dim year'[Year]) //disconnected slicer

VAR CumulativeValue =

    CALCULATE(

        SUM('Investment'[Investment]),

             'dim CALENDAR'[Dates] >= SelectedYear &&

             'dim CALENDAR'[Dates] <= MAX('Investment'[Dates]),

              ALL('dim CALENDAR'[Dates])

        )

   RETURN CumulativeValue

And how would I add a filter for the year range to this?
The start year has to be selected in a slicer.

[lbendlin]

Investment = 
VAR SelectedYear = SELECTEDVALUE('dim year'[Year]) //disconnected slicer
VAR CumulativeValue =
    CALCULATE(
        SUM('Investment'[Investment]),
              ALL('dim CALENDAR'[Dates]),
             YEAR('dim CALENDAR'[Dates]) >= SelectedYear,
             YEAR('dim CALENDAR'[Dates]) <= SelectedYear+9
        )
   RETURN CumulativeValue

[MarianneElver]

Hi,

This solution unfortunately returns 5000 (the total investment sum of 10 * 500) on every year

[lbendlin]

What is your expected outcome?

[MarianneElver]

The expected outcome is 500 the first year, and then the accumulated value with +500 each of the following years, so that in the 10th year, I reach 5000.

[mangaus1111]

Hi Marianne,

maybe you have to remove the statement ALL('dim CALENDAR'[Dates]) from your measure.

[MarianneElver]

Hi mangaus,
Thanks for your reply.
When I remove the ALL filter, it only shows investmenst on the selected year!

[lbendlin]

you seem to be missing the "plus ten years"  filter?

[MarianneElver]

Thanks for your reply.

I have tried this, to include the 10 years, but that did not work:

Investering sim fra første inv-år mio. kr. (EOS) =

VAR SelectedYear = SELECTEDVALUE('dim year'[Year])
VAR YearsToCalculate = ADDCOLUMNS(
GENERATESERIES(SelectedYear,SelectedYear + 9),
"CumulativeYear",
[Value],
"CumulativeValue",
CALCULATE(
     SUM('Investment'[Investment]), 
FILTER(
ALL('dim year'[Year]),
'dim year'[Year] >= SelectedYear &&
'dim year'[Year]<= MIN([Value])
)
)
)
RETURN
SUMX(YearsToCalculate, [CumulativeValue])

Any suggestions?

Thanks,

Marianne

[lbendlin]

Try something less complicated. Keep your ALL filter but add a filter for the year range.  No need for generateseries, addcolums etc.