Forum Discussion
Sum data dependent on two different date filters
I am currently getting data from our Microsoft Dynamics AX tables. We have our sales quantities that when filtered by invoice date will give us the quantity that's been invoiced to date. We also have our sales quantities that when filtered by confirmed delivery date will provide us with the quantity that is in Open Order or Delivered status. The data for sales quantity is the same for both instances i.e. we use SALESQTY to get both invoiced or open & delivered sales quantities. The issue is that the invoice date doesn't provide us with the open order/delivered quantities, and the confirmed delivery date doesn't give us the invoiced quantities. I want to add these two together in the same column.
Essentially I want:
(SALESQTY(Filtered by Invoiced Date - Current Month/Year)+(SALESQTY(Filtered by Confirmed Delivery Date - Current Month/Year & Sales Line Status = "Open Orders" & "Delivered )
This might be an easy solution, I just haven't gotten it to work correctly.
4 Replies
Blog on dealing with two dates, very similar : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedinamitchandak I've created the date table as you laid out, however, you end up using a Count function, whereas I need to use a Sum function for the quantity and still be able to filter the data by date.
Using SUMX, in place of COUNTX, is not going to solve the purpose?
