Need help on DAX forumlae to calculate the Sale order count that has invoiced and uninvoiced

[NH]

Hi

Hope someone can help to advise me the DAX forumale to count Sale order that has invoiced and no invoiced every month based on the  sale order table, invoice table. I've also created a date table.

Scenario is:

1. If the Sale  order month = Invoiced Month then count this SO ID has invoiced

2. If the Sale order has no invoiced date then count this SO ID as uninvoiced and has to check again for following month and so till it has invoiced date. 

3. Sale order that has invoiced on the current month (report month) will not included or count in the following month.

Example of my Sale Order table contain Sale Order ID ( SO ID) and Sale order date, Qty

Sale Order Table
SO ID	SO date	Qty
1111	12-Dec-17	10
2222	13-Dec-17	20
3333	14-Dec-17	15
4444	15-Dec-17	4
5555	2-Jan-18	1
6666	3-Jan-18	3
7777	4-Jan-18	4
8888	18-Jan-18	5
9999	19-Jan-18	7

Invoiced Table

Invoice Table
SO ID	Invoiced Date	Invoice $
1111	16-Dec-17	100
2222	17-Dec-18	200
3333	3-Jan-18	150
4444	2-Feb-18	40
5555	4-Jan-18	10
6666	7-Jan-18	30
7777	4-Feb-18	40
8888	3-Mar-18	50
9999	4-Mar-18	70

Expected Result

Output report
Month Yr	Invoiced SO count	Uninvoiced SO count	Remark
Dec 2017	2	2	1111 & 2222 has invoiced in Dec while 2222 and 3333 was only happen to invoiced in Jan and Feb respectively
Jan 2018	3	4	3333 was invoiced in Jan 18 with 5555, 6666 SO thus total Invoiced SO count for Jan was 3 while Uninvoiced SO count was 4 becos 4444, 7777, 8888, 9999 the invoiced date was happen on the following month or next

Thanks for the help in advance.

NH

[afzalphatan]

willl give it a try soon....

[Zubair_Muhammad]

Hi @NH

Please see the attached file here

Uninvoiced SO Count =
VAR AllOrders =
    CALCULATETABLE (
        VALUES ( SalesOrder[SO ID] ),
        FILTER (
            SalesOrder,
            SalesOrder[SO date] <= EOMONTH ( MAX ( Invoice[Invoiced Date] ), 0 )
        )
    )
VAR Allinvoices =
    CALCULATETABLE (
        VALUES ( Invoice[SO ID] ),
        FILTER (
            ALL ( Invoice ),
            Invoice[Invoiced Date] <= MAX ( Invoice[Invoiced Date] )
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( AllOrders, Allinvoices ) )

[NH]

Still not able to figure out the correct DAX forumale. Your solution did help to provide the ideal.

[NH]

Hi Zubair,

Appreaciate your prompt help. I will try it out and will update you soon. A quick check on your sample pbix file and notice the SO ID count is not what I'm looking for. 

The rule is count of Invoiced SO is:

SO ID in Sale oder table =  SO ID in the invoiced table

and

Sale order Month = Invoiced Month

Count = 1

Else if Sale order month < invoice Month (subsequce month, can be happen in m+1 o m+2, etc)

then consider this count of invoiced SO in the M+1 or M+2 , etc same the report month 

Sorry if my explaination is not clear

Thank you.