IF Statement, move values between cells

[VTork]

Hi all, 

I'm trying to come up with the right IF And statement for my power query, but I'm still quite new to the tool and having some troubles when trying to find the right DAX formula. Hopefully there's someone here who would be able to help 🙂  

What I'm trying to do is for a value to be added to a specific cell (here Year (2023, 2024 etc) depending on it's value.

- If it's less than 250k, it should be added to the cell that equals "Order Year + 2 years", so if "Order Year" = 2022, the Value should be added to the "2024" columnd. I think I've managed this for now.  
- Next step is where I struggle. If the value is between 250K and 750K, I'd like 250K to be added to the column that equals "Order Year + 2 Years" and the rest to be added to the column that equals "Order Year + 3 years". For example, should the value be 350K and the order year is 2024, I'd like 250K to be added to the 2026 column, and the rest (100K) to be added to the 2025 column. 
- Finally, if the Value is greater than 750K, I'd like it to be added to the column which equals "Order Year + 4 years" - so if the value is 800K and the order year is 2026, the value should be added to the 2030 column. 

Any help will be much appreciated - thanks! 🙂 

[v-stephen-msft]

Hi @VTork ,

You could first create a what-if parameter to generate an order year, this can be dynamic, you are free to choose.

If your values are text containing "k", you need to extract the number in the text first.

Year = var _value=MAX('Table'[Value])
var _num=VALUE(LEFT(_value,LEN(_value)-1))
var _orderyear=SELECTEDVALUE(Parameter[Parameter])
return IF(_num<250,_orderyear+2,IF(_num>=250&&+_num<750,_orderyear+3,IF(_num>=750,_orderyear+4)))

If they are pure numbers, as long as the formula below is followed, I will not create sample data examples.

Year = var _num=SUM('Table'[Value])
var _orderyear=SELECTEDVALUE(Parameter[Parameter])
return IF(_num<250,_orderyear+2,IF(_num>=250&&+_num<750,_orderyear+3,IF(_num>=750,_orderyear+4)))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sturlaws]

Hi,

not sure of how you have labelled you columns or structure of your data, since you are not sharing any screen shots, pbix-file og data table.

I have created an example of how it could be done if you have the columns OrderYear, OrderSum, OrderYear+2,...., OrderYear+4, attached at the bottom of my reply

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[VTork]

Amazing – this helped, thank you @sturlaws ! Perhaps you would be able to advice on the next step too, which I’m working on now…

To see the years the values refer to, I created a few more year columns - please see image 1. I then wanted to calculate the total value for each Order Year, which I’ve done by referencing the main table, then grouping and merging – please see the final outcome in image 2. However, my issue now is that when creating a line chart within the desktop is that the values cannot be filtered by data in the initial table due to the change in columns. I’ve probably overcomplicated the process for myself, but is there an easier way around this or a way where I can create a link between the Total Values table and initial table?  

Many thanks!

Image 1Image 2