Hi Guys
I decided to post a new question related to my previous related post that the community helped me on
How to return the right most value in a row using Power Query
Of which the below expression provided did help, however after which I realized that I need to return the left or right most value of specific ranges to used in a follow up formula of which I not entirely sure how to do.
So This is My Dataset:
Using row 13 in the above picture an example I am looking for 3x Custom Column formulas that will calculate the following answers:
1. 2020 / 2021 1st Price
1.1. Return the 1st or left most (Amt) value in range 01.Jan (Amt) to 12.Dec (Amt) (i.e. R139 230)
1.2. Divide it by
1.3. Return the 1st or left most (Qty) value in range 01.Jan (Qty) to 12.Dec (Qty) (i.e. 600.00)
1.4. Ensure that any possible errors caused by the division returns Null
=R232.05
2. 2020 / 2021 Last Price
1.1. Return the Last or Right most (Amt) value in range 01.Jan (Amt) to 12.Dec (Amt) (i.e. R48 730)
1.2. Divide it by
1.3. Return the Last or Right most (Qty) value in range 01.Jan (Qty) to 12.Dec (Qty) (i.e. 200.00)
1.4. Ensure that any possible errors caused by the division returns Null
=R243.65
3. 2020 / 2021 Average Price
1.1. 2020 / 2021 Total (Amt) (i.e. R555 423)
1.2. Divide it by
1.3. 2020 / 2021 Total (Qty) (i.e. 2 289.00)
1.4. Ensure that any possible errors caused by the division returns Null
=R242.65
This last formula should not be to much of issue
Any help would be much appreciated...
I decided to post a new question related to my previous related post that the community helped me on
How to return the right most value in a row using Power Query
Of which the below expression provided did help, however after which I realized that I need to return the left or right most value of specific ranges to used in a follow up formula of which I not entirely sure how to do.
So This is My Dataset:
Using row 13 in the above picture an example I am looking for 3x Custom Column formulas that will calculate the following answers:
1. 2020 / 2021 1st Price
1.1. Return the 1st or left most (Amt) value in range 01.Jan (Amt) to 12.Dec (Amt) (i.e. R139 230)
1.2. Divide it by
1.3. Return the 1st or left most (Qty) value in range 01.Jan (Qty) to 12.Dec (Qty) (i.e. 600.00)
1.4. Ensure that any possible errors caused by the division returns Null
=R232.05
2. 2020 / 2021 Last Price
1.1. Return the Last or Right most (Amt) value in range 01.Jan (Amt) to 12.Dec (Amt) (i.e. R48 730)
1.2. Divide it by
1.3. Return the Last or Right most (Qty) value in range 01.Jan (Qty) to 12.Dec (Qty) (i.e. 200.00)
1.4. Ensure that any possible errors caused by the division returns Null
=R243.65
3. 2020 / 2021 Average Price
1.1. 2020 / 2021 Total (Amt) (i.e. R555 423)
1.2. Divide it by
1.3. 2020 / 2021 Total (Qty) (i.e. 2 289.00)
1.4. Ensure that any possible errors caused by the division returns Null
=R242.65
This last formula should not be to much of issue
Any help would be much appreciated...