Forum Discussion
Self-referencing calculated column
Hello!
In PowerBI I have loaded a spreadsheet with historical records of a factory. It contains the following columns:
- SKU
- Date
- Demand
- Real Physical Stock (SFR)
- Stock in real transit (STR)
- Stock objective (SO)
- Shopping Day (DC)
- Day Number (ND)
- Fictitious entry (EF)
- MOQ
From this information, I need to generate a new column called 'Fictitious Physical Stock (SFF)', which should be calculated as follows:
- If NDC < 1, then it must be equal to SFR.
- If NDC = 1, it should be calculated as the SFR of the previous day plus the EF of the day the calculation is performed minus the demand of the day the calculation is performed.
- If NDC > 1, it should be calculated as the SFF of the previous day plus the PE of the day the calculation is performed minus the demand of the day the calculation is performed.
I have the problem in the third condition, since I need the value of the previous day of the same calculation I am performing (SFF). Could someone help me solve it?
Thank you very much in advance!
2 Replies
Check this if it works :
IF([Day Number] < 1, [Real Physical Stock (SFR)],
IF([Day Number] = 1, [Real Physical Stock (SFR)] + [Fictitious entry (EF)] - [Demand],
LAG([Fictitious Physical Stock (SFF)],1) + [Fictitious entry (EF)] - [Demand]))
if it is solved kindly accept as a solution .
ThanksThank you very much for the reply.
This possible solution would not be working for me, since it does not recognize the GAL function, nor the SFF value within this function:Best regards.
