Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
horsebaker
Regular Visitor

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



1 ACCEPTED SOLUTION
ahadkarimi
Solution Specialist
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])))

View solution in original post

1 REPLY 1
ahadkarimi
Solution Specialist
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])))

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors