Ten capacity projects with slicer selectable years to add capacity to baseline capacity.

[Anonymous]

I have a table of capacity by year for ten years:

Year	Capacity
2020	100
2021	120
2022	150

and a table with several projects and the resulting capacity that they add:

Project #	Capacity Addition
1	10
2	20
3	30

I also made an individual table for every project that contains all the possible year choices and None:

PROJECT1

Selected Year

None

2020

2021....

I need a visualization that lets you select a year via a slicer for each project (or None). The capacity addition for that project then gets added to the capacity for that year and all years after that. For example, if 2021 is select for Project 1 then the capacity table would look something like this:

Year	Capacity	New Capacity
2020	100	100
2021	120	130
2022	150	160

I was able to create a measure that becomes the selected year fairly easily, but the problem I'm having is that you cannot use measures in Calcualted Columns because the aren't dynamic. So this doesn't work.:

IF(IFERROR(CONVERT(SELECTEDVALUE(Project[Selected Year],10000),INTEGER),10000)<= 'Sum Table'[Year],'Sum Table'[Capacity]+LOOKUPVALUE('Project Data'[Total Capacity Added],'Project Data'[Roadmap Projects],"1"),0)+
IF(SELECTEDVALUE('Project (2)'[Selected Year],10000)<='Sum Table'[Year],'Sum Table'[Capacity]+LOOKUPVALUE('Project Data'[Total Capacity Added],'Project Data'[Roadmap Projects],"2"),0)+
IF(SELECTEDVALUE('Project (3)'[Selected Year],10000)<='Sum Table'[Year],'Sum Table'[Capacity]+LOOKUPVALUE('Project Data'[Total Capacity Added],'Project Data'[Roadmap Projects],"3"),0)

To throw another wrench in the mix, there are multiple baseline capacity options that I need to be able to select from. They are all in the same table. 

How can I get this to work?

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Please check whether the below measure is what you want.

New Capacity = 
VAR _selproj =
    SELECTEDVALUE ( 'Projects'[Project #] )
VAR _additionalcap =
    CALCULATE (
        MAX ( 'Projects'[Capacity Addition] ),
        FILTER ( 'Projects', 'Projects'[Project #] = _selproj )
    )
RETURN
    MAX ( 'Capacity'[Capacity] ) + _additionalcap

If the above measure is not suitable for your scenario, please provide more sample data in your model and your expected result using data examples. Thank you.

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

I was able to use the solution I accepted to create the code that I needed. Ulitimately it looked like this:

RETURN
    IF(MAX('Sum Table'[Year])>=yr1,cap1,0)+
    IF(MAX('Sum Table'[Year])>=yr2,cap2,0)+
    IF(MAX('Sum Table'[Year])>=yr3,cap3,0)+
    IF(MAX('Sum Table'[Year])>=yr4,cap4,0)+
    IF(MAX('Sum Table'[Year])>=yr5,cap5,0)+
    IF(MAX('Sum Table'[Year])>=yr6,cap6,0)+
    IF(MAX('Sum Table'[Year])>=yr7,cap7,0)+
    IF(MAX('Sum Table'[Year])>=yr8,cap8,0)+
    IF(MAX('Sum Table'[Year])>=yr9,cap9,0)+
    SUM('Sum Table'[Capacity])

Where each yr is the slicer selection for the year as an int (if it fails to convert then goes to 10000) and each cap is the capacity for a project.