Sum data dependent on two different date filters

[Anonymous]

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.

[amitchandak]

Blog on dealing with two dates, very similar : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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 Linkedin

[Anonymous]

@amitchandak 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.

[amitchandak]

Using SUMX, in place of COUNTX, is not going to solve the purpose?

[Anonymous]

@amitchandakI haven't been able to get it to work. Let me lay out the Tables and Data and see if you can clarify for me.

Table: SalesLine

Data:

SalesLine[Salesqty]

SalesLine[Confirmeddlv]

Table:CustInvoiceTrans

Data:

CustInvoiceTrans[Invoiceddate]

Table:Date

Data:

Month Year

Based on these I want to have SalesLine[Salesqty] filtered by SalesLine[Confirmeddlv] + SalesLine[Salesqty] filtered by CustInvoiceTrans[Invoiceddate]

I currently have the Date table with an inactive relationship to the SalesLine[Confirmeddlv] & CustInvoiceTrans[Invoiceddate]. Both are Many to one and single direction.