Forum Discussion

horsebaker's avatar
horsebaker
Regular Visitor
2 years ago
Solved

Removing Sales position using different date

Dear community!

The problem I am encountering:
I have Sales data with Item positions. It is considered Orders received with "Document Date" attached to this position.
However, I also have another column which tags an item as "rejected/cancelled", which sometimes happen. This then have "Item changed date".

With this in mind, I want to create a time intelligence/Matrix that pick up;
Original order for Document date that gets removed for what date it is found to be rejected, but original position does not get adjusted;
I.e if 5 items for sales order is received in February, and 1 item is removed in May, the OR should still have 5 items in Feb, but item 1 is removed in May.

The only solution I have found removes the item on February level.

Your support would be much appreciated!

Regards,
Horsebaker



  • If you have any questions or need more info, just hit me up!

    Original Orders =
    CALCULATE(
    SUM(Sales[ItemCount]),
    Sales[Status] <> "Rejected")
    ----------------------------------
    Rejected Items =
    CALCULATE(
    SUM(Sales[ItemCount]),
    Sales[Status] = "Rejected")
    -----------------------------
    Adjusted Orders =
    CALCULATE(
    [Original Orders] - [Rejected Items],
    DATESBETWEEN(
    'Calendar'[Date],
    MIN('Calendar'[Date]),
    MAX('Calendar'[Date])))

1 Reply

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist

    If you have any questions or need more info, just hit me up!

    Original Orders =
    CALCULATE(
    SUM(Sales[ItemCount]),
    Sales[Status] <> "Rejected")
    ----------------------------------
    Rejected Items =
    CALCULATE(
    SUM(Sales[ItemCount]),
    Sales[Status] = "Rejected")
    -----------------------------
    Adjusted Orders =
    CALCULATE(
    [Original Orders] - [Rejected Items],
    DATESBETWEEN(
    'Calendar'[Date],
    MIN('Calendar'[Date]),
    MAX('Calendar'[Date])))