Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
willl give it a try soon....
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 ) )
Still not able to figure out the correct DAX forumale. Your solution did help to provide the ideal.
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.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
98 | |
89 | |
73 | |
62 | |
61 |