topic Re: Adding conditional column with either value from other column OR the value from the row above in DAX Commands and Tips

Adding conditional column with either value from other column OR the value from the row above

DanielB_NL — Sat, 15 Jun 2024 21:50:51 GMT

Hi All,

I'm still trying to find my way in PowerBi and to solve some interesting questions, which I have.

I have this query that generates the upper table: it put some expected mutations for a specific item no. in a time line and checks whether theoretticaly the stock is not sufficient. So this is nice, but now I want to see what my options are if the stock is not sufficient for the expected mutation:

If there is an expected Purchase Order due to arrive just before the mutation, I would like to know when and what amount: if I know which Purchase Order I can call the supplier to try to increase the ordered amount
If there is an expected Purchase Order due to arrive just after the mutation, I would like to know when and what amount: if I know which Purchase Order I can call the supplier to try to get the items easlier.

Can someone tell me how to add such a conditional column? So for 'Previous_purchase' I would like to check if the column 'type' has the value 'Purchase' on the previous row and if so, I woulde like to get the Reference and put it in the column 'Previous_purchase'. If the column 'type' has not the value 'Purchase' on the previous row, then I would like in 'Previous_purchase' to repeat the value of the previous row.

If I'm able to make such an overview, I only have to look at the row with the negative stock after mutation, and I can see directly which Purchase Order(s) are involved.

I hope you can help me out, thanks in advance!

Re: Adding conditional column with either value from other column OR the value from the row above

lbendlin — Sat, 15 Jun 2024 23:42:05 GMT

There are a couple of tools at your disposal. Read about nested IFs, SWITCH, and COALESCE. Pick whatever you like best.

Re: Adding conditional column with either value from other column OR the value from the row above

DanielB_NL — Sun, 16 Jun 2024 20:35:26 GMT

I'm working with swicht and nesteds IF's, but I guess I have to start with getting the value of 'reference' for the row direct after a Purchase row: I've tried and googled a while, and came up with the code below, but it's not working.

previous_purchase = VAR CurrentRowType = Todays_future[type] VAR PreviousRowType = CALCULATE(MAX(Todays_future[type]), FILTER(ALL(Todays_future), Todays_future[index] = EARLIER(Todays_future[index]) - 1)) RETURN IF(CurrentRowType = "Purchase (PO)", Todays_future[reference], BLANK())

Re: Adding conditional column with either value from other column OR the value from the row above

lbendlin — Mon, 17 Jun 2024 00:02:56 GMT

Try not to use EARLIER. Use variables instead.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Adding conditional column with either value from other column OR the value from the row above

DanielB_NL — Mon, 17 Jun 2024 14:27:47 GMT

Hi, thank you for the swift reaction. Variables is competely new to me, so I really hope you can help me a little further. I've created a small dataset in an CSV-file and made it available from my Dropbox account:

https://www.dropbox.com/scl/fi/rfa20o4q1uq157ha9gdwg/use_case_PowerBI_data.csv?rlkey=i2x9ag9asd99m0g4pdmc29cb4&st=uh2gnb5o

I've created an Excelsheet with the desired results:

https://www.dropbox.com/scl/fi/ufdqndnkkoozuqcesk79x/use_case_PowerBI_desired_result.xlsx?rlkey=yfeo19f19xdxy6tlnepez8t80&st=tz7ym6gq

If I have the extra columns, in my Dashboard I can filter on rows that have in the column 'Stock_after_booking' a value < 0. By doing this I isolate all my project which have a potential problem and I also see which Purchase Orders I can try to influence in order to solve the problem.

I really hope you can help me with this, thanks in advance for your reply!

Kind regards, Daniel

Re: Adding conditional column with either value from other column OR the value from the row above

lbendlin — Thu, 20 Jun 2024 23:01:58 GMT

Thank you for the sample data. Looking at it I don't see any fields that would be impacted by user filter choices. As such the new columns could be added in Power Query instead of DAX. Do you have any preferences?

Re: Adding conditional column with either value from other column OR the value from the row above

DanielB_NL — Fri, 21 Jun 2024 08:57:20 GMT

Hi lbendlin, PowerQuery would be fine as wel!! I don't have any preferences and am already very happy that you are putting your time and effort in it

Re: Adding conditional column with either value from other column OR the value from the row above

DanielB_NL — Sun, 23 Jun 2024 21:48:04 GMT

Hi lbendlin, I've extended the dataset with 2 more article numbers: the idea that the stock mutations over time are evaluated per article number, but after looking at the sample data again I wasn't sure that this was clear with the limited data sample. I think it is more clear this way.

Re: Adding conditional column with either value from other column OR the value from the row above

lbendlin — Sun, 07 Jul 2024 21:21:23 GMT

I'm a bit lost as to what you are trying to achieve. Are you trying to predict when you run out of stock?

Your sample data may not be sufficient for the scenario.

Re: Adding conditional column with either value from other column OR the value from the row above

DanielB_NL — Sun, 07 Jul 2024 21:35:45 GMT

Yes I am. We have 4 types of mutations that influence the stock level:

Purchase: receiving purchase orders
Production: our own production from our shop factory
Sales: sales via our Service Deparment
Project: Usage on projects that are being assembled

My idea is

That mutations are done in planned order, so I can put them ordered by date and see how stock fluctuates
For mutations of type 3 and 4 there is a problem if after the mutation stock is below zero (of before the mutation, the mutation amount is bigger than the stock level)
I want our Purchase Department to react and my first impression is that I can have up to 2 possibilities:
- If there is a purchase reception planned before the expected stock shortage: I can try to ask the supplier to increase the amount on that Purchase Order, but I need to now which Purchase Order I need to look at
- If there is a purchase reception planned after the expected stock shortage: I can try to ask the supplier to deliver earlier, but also for this goes that I need to know which Purchase Order to look at.
- Also for the Purchase reception that is planned after the expected stock shortage: if the difference in dates is less than or equal to 7 days, I can neglect them because for me that is still acceptable and not worth the effort to get the delivery a few days faster.

I hope you have an idea now of what I try to achieve

Re: Adding conditional column with either value from other column OR the value from the row above

DanielB_NL — Fri, 26 Jul 2024 10:04:01 GMT

Hi lbendlin, can you tell me whether it is technically possible? Or do I need more, additional data? I'm still hoping you can help me out.

Re: Adding conditional column with either value from other column OR the value from the row above

lbendlin — Fri, 26 Jul 2024 20:38:38 GMT

Keep in mind that Power BI is a reporting tool, not a stock/inventory management application. May want to use a proper app.