Trouble getting the cumulative line to display over all years rather than sliced year

[MarkD1722]

I have a combined stacked column and line chart that displays individual values and cumulative value for selected projects.  I am having trouble getting the cumulative value to display over all dates for all years relevant to the projects that result from another measure (which is defined by the [Project Activity] filter below) which is calculated from the year selected (i.e., from the Year List used as a slicer).  I get all the relevant values displaying in the chart, but the cumulative line starts with the first value of the year selected and ends with the final value of year selected.  In the image below, I want that blue line to continue over all the values you see.

Below is my DAX measure for the cumulative line.  I recently learned about the ALLCROSSFILTERED function and thought that would do the trick as it solved several other measures.  But it's not working as I had hoped.  Can anyone provide some guidance/insight as to what I need to do to get the full line to display.  Below is the DAX measure I have.  The VARiables are for the purposes of currency exchange, and I don't think the currency exchange affects the line, but it may.

Thanks in advance for your ideas and help.

Cumulative Line =

VAR

    Select_Curr = SELECTEDVALUE(Currency_Exchange_Rate[Currency])

VAR Value_Yr_Mon = MAX('Final Value'[Value Year_Month])

VAR

    Resulting_Exch_Rate = CALCULATE(MAX( 'currency_exchange_rate_table_gold'[Exchange_Rate])

                                    , 'currency_exchange_rate_table_gold'[Currency] = Select_Curr

                                    , 'currency_exchange_rate_table_gold'[Currency Year_Month] = Value_Yr_Mon)

RETURN

CALCULATE(

      SUM('Final Value'[Amount USD])

            , ALLCROSSFILTERED('Year List')  //Year List is a table that provides the primary key for year selection.

            , FILTER('PROJECT LIST', 'Project Central'[Project Activity] IN { "Current spend", "Current activity", "Future activity", "Other activity" } )

            , 'Final Value'[DATE]<= MAX('Final Value'[DATE])

        )

* Resulting_Exch_Rate

[MarkD1722]

Thanks for all the help suggestions.  The solution was a design change to the data model.  What we did was create another table in Power Query that listed all projects along with "active years" using the logic from the measure I mentioned in the original post.  One column is "Active Year" and the other is simply the project number.  This allowed us to connect our year list to the active year and also connect the the project numbers to the orignal project list, of which there is only one project number per line.  In turn, those projects are connected to the fact table of values.  In summary, there were two simple 1-to-many relationships that allowed the both the individual values and the cumulative value measure to work just fine as they were originally designed. 

The only additional thing outside this situation was that some other relationships could not be active or it would completely mess everything up.  In these instances, I used "inactive relationships" and the USERELATIONSHIP and ALLCROSSFILTERED functions to make everything work correctly.   I had not leverged those functions before.  It seems like it was easier to not have the active relationship and activate it in the specific measures than to have an active relationship and deactivate it for the measures.  Is there a general truth to that?  Or is it a case-by-case situaiton?

Thanks again for you help!

[Anonymous · Accepted solution]

Hi @MarkD1722 ,

Thanks for the reply from @Greg_Deckler .

You want to display the cumulative values ​​for all years and don't want to skip Year2, right?

If so, you can use the ALL function instead of the ALLCROSSFILTERED function.

The ALL function removes all filter contexts.

The adjusted measure syntax is as follows:

Cumulative Line =
VAR Select_Curr = SELECTEDVALUE(Currency_Exchange_Rate[Currency])
VAR Value_Yr_Mon = MAX('Final Value'[Value Year_Month])
VAR Resulting_Exch_Rate = CALCULATE(
MAX('currency_exchange_rate_table_gold'[Exchange_Rate]),
'currency_exchange_rate_table_gold'[Currency] = Select_Curr,
'currency_exchange_rate_table_gold'[Currency Year_Month] = Value_Yr_Mon
)
RETURN
CALCULATE(
SUM('Final Value'[Amount USD]),
ALL('Year List'[Year]),
FILTER(
'PROJECT LIST',
'Project Central'[Project Activity] IN { "Current spend", "Current activity", "Future activity", "Other activity" }
),
'Final Value'[DATE] <= MAX('Final Value'[DATE])
) * Resulting_Exch_Rate

If the adjusted syntax does not solve your problem, please provide me with the pbix file.

Remember to remove sensitive data and do not log in to your account in Power BI Desktop when uploading the pbix file.

If you have any other questions, please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Greg_Deckler]

@MarkD1722 See if this helps: Better Running Total - Microsoft Fabric Community If not, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.