SUM Values for specific Dates in different columns

[VTork]

Hi everyone, 

I got a table with various data and have calculated the expected value for the coming years. Some values have been divided between cells due to assumptions within the table, hence "Y1" & "Y1 Value", "Y2" & "Y2 Value" etc. Please see the image attached. Next, I would like to create a line chart that shows the total sum for each year for the next 10 years - something like the line chart example attached but with Year 2022-2033. 

I've managed to do this by grouping and merging through a new query, but this disrupt the relationship with the initial query as I'm not including the identity column (if I do I'm back to square one), thus the line chart won't react to any filtering within the desktop. 

So my question is - is there a DAX formula I could use or a way around this within the query or the desktop to create a new measure that will calculate the total values depending on the various year columns? Still quite new to Power Bi and struggle to find a solution for this 😅 any help will be much appreciated! Example Line ChartYears & Value

[VTork]

Hi @lbendlin - good idea. 

The visual within the sample shows how I'd like to present it, but the issue I’m having is that it can't be filtered by product/region etc for all tables – Y1, Y2, Y3... 
If I’m using the product & region column for Y1, it’ll only show the values within that table and not the total for all - if I'm using the initial table, non of them will be filtered.. 

However, before going too much into the above, the plan of my calculation has changed slightly, as I'd like to include the 10 Year Value into my calculation per year as well. Subsequently, for those values to be part of the total value per year within the visual. 

The first assumptions here is like my initial calculations: 

• Our Y1 will be two years after Order Year (example: Order Year = 2023, then Y1 = 2025, Y2 = 2026, Y3 = 2027)
• Y1 = if [Typical Value] <= 250 then [Typical Value] else if [Typical Value] <= 750 then 250 else if [Typical Value] > 750 then 0 else 0
• Y2 = if [Typical Value]>250 and [Typical Value]<750 then ([Typical Value]-[Y1 Value]) else 0
• Y3 = if [Typical Value] > 750 then [Typical Value] else 0

However now I'd like to add the following assumption, but not sure how to do it: 

If Typical Value = 100, 10 Year Value = 150, Y1 = 2025, then I'd like the remaining 10 Year Value (being 150-100=50), so 50, to be split between the following years up to 2033.
Meaning, 50/8 = 18.75, and as Y1 = 2025 I'd like 50 to be split between year 2026 to 2033 à 2026 = 18.75, 2027 = 18.75 etc for that specific row (and this to be done for all rows for the empty columns up to 2033).

Example below, but here I'm showing the years in the columns which I know wouldn't work. 

ID	Region	Project Name	Car Type	Total Quantity Required	Product	Typical Value	10 Year Multiple	10 Year Value	Y1	Y1 Value	2026	2027	2028	2029	2030
1	Asia	Alpha	Audi	3	B	100	0.5	150	01/01/2025	100	18.75	18.75	18.75	18.75	18.75

I'm still new to DAX so unsure on how I can find my way around this without having to create multiple tables & measures, which will then cause relationships to break, and I'd be unable to filter by product, region etc – as my initial problem.

Hope that makes sense - Thank you 🙂 

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[VTork]

@lbendlin - are you still able to help on this? I'm completely stuck and would really appreciate your support! 😇

[lbendlin]

See attached for a version using a composite key.

[VTork]

Hi @lbendlin - good idea. 

I've created a sample data which can be found here - https://drive.google.com/drive/folders/1ttd6M2S2Cnxx4-QgxtTXOyS7VfcoNBou?usp=share_link 

The visual within the sample shows how I'd like to present it, but the issue I’m having is that it can't be filtered by product/region etc for all tables – Y1, Y2, Y3... 
If I’m using the product & region column for Y1, it’ll only show the values within that table and not the total for all - if I'm using the initial table, non of them will be filtered.. 

However, before going too much into the above, the plan of my calculation has changed slightly, as I'd like to include the 10 Year Value into my calculation per year as well. Subsequently, for those values to be part of the total value per year within the visual. 

The first assumptions here is like my initial calculations: 

• Our Y1 will be two years after Order Year (example: Order Year = 2023, then Y1 = 2025, Y2 = 2026, Y3 = 2027)
• Y1 = if [Typical Value] <= 250 then [Typical Value] else if [Typical Value] <= 750 then 250 else if [Typical Value] > 750 then 0 else 0
• Y2 = if [Typical Value]>250 and [Typical Value]<750 then ([Typical Value]-[Y1 Value]) else 0
• Y3 = if [Typical Value] > 750 then [Typical Value] else 0

However now I'd like to add the following assumption, but not sure how to do it: 

If Typical Value = 100, 10 Year Value = 150, Y1 = 2025, then I'd like the remaining 10 Year Value (being 150-100=50), so 50, to be split between the following years up to 2033.
Meaning, 50/8 = 18.75, and as Y1 = 2025 I'd like 50 to be split between year 2026 to 2033 à 2026 = 18.75, 2027 = 18.75 etc for that specific row (and this to be done for all rows for the empty columns up to 2033).

Example below, but here I'm showing the years in the columns which I know wouldn't work. 

ID	Region	Project Name	Car Type	Total Quantity Required	Product	Typical Value	10 Year Multiple	10 Year Value	Y1	Y1 Value	2026	2027	2028	2029	2030
1	Asia	Alpha	Audi	3	B	100	0.5	150	01/01/2025	100	18.75	18.75	18.75	18.75	18.75

I'm still new to DAX so unsure on how I can find my way around this without having to create multiple tables & measures, which will then cause relationships to break, and I'd be unable to filter by product, region etc – as my initial problem.

Hope that makes sense - Thank you 🙂