Hi guys,
I'm currently facing an issue to implement a bookkeeping issue in my power bi dashboard.
Normally we record an order intake at a specific Date.
For example, in the SAP period 06/2022 we got an order at that date 16.06.2022.
My output looks like this:
| SAP Period | Order | Material | Date | Revenue | Order Intake |
| 06/2022 | 123456 | M1 | 15.06.2022 | | 500.000 |
| 06/2022 | 123456 | M2 | 15.06.2022 | | 250.000 |
| 06/2022 | 123456 | M3 | 15.06.2022 | | 1.000.000 |
A few months later, the order is fullfilled and it should look like this:
| SAP Period | Order | Material | Date | Revenue | Order Intake |
| 01/2023 | 123456 | M1 | 20.01.2023 | 500.000 | |
| 01/2023 | 123456 | M2 | 20.01.2023 | 250.000 | |
| 01/2023 | 123456 | M3 | 20.01.2023 | 1.000.000 | |
So far, so good. As expected, when I get my order, I record an order intake, when I fullfill the order, I have a revenue.
Unfortenately, sometimes the order intake needs to be modified at the date of the fullfillment and my output looks like this:
| SAP Period | Order | Material | Date | Revenue | Order Intake |
| 01/2023 | 123456 | M1 | 15.06.2022 | | -500.000 |
| 01/2023 | 123456 | M2 | 15.06.2022 | | -250.000 |
| 01/2023 | 123456 | M3 | 15.06.2022 | | -1.000.000 |
| 01/2023 | 123456 | M1 | 20.01.2023 | 500.000 | 500.000 |
| 01/2023 | 123456 | M2 | 20.01.2023 | 250.000 | 250.000 |
| 01/2023 | 123456 | M3 | 20.01.2023 | 1.000.000 | 1.000.000 |
Explenation: in the SAP Period 01/2023 the order intake from 15.06.2022 was canceled and the created a new order intake at the date of the revenue. In total the order intake will still be 0 but I have the recorded data in my dashboard.
So in the end 01/2023 will look like this:
| SAP Period | Order | Material | Date | Revenue | Order Intake |
| 01/2023 | 123456 | M1 | 20.01.2023 | 500.000 | - |
| 01/2023 | 123456 | M2 | 20.01.2023 | 250.000 | - |
| 01/2023 | 123456 | M3 | 20.01.2023 | 1.000.000 | - |
But I (by boss) does not want to the those canceled and redone order intakes in the Dashboard as they will only confuse the audience and don't have any value in this report.
Is there any way for me to create a measure that sais, if I have an order number and the sum of the order intake of a material number is 0, then dont't show the order intake at all, but only the revenue.
It's quite abtract, but I can't think of any other sollution right now.
Thanks!!!
