Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to account for two exceptions in my Sales data by creating a separate table.
1. When a credit/rebill is done to change the client code of the bill. This causes the data to have the same bill number show up twice in my data with different customer names. In this case I need the Max of the created date.
In the second case there may be a separate charge associated with the bill that is billed to a third party. The Bill number will be the same but will have a "A", "P" or "E" appended. In this case I need the Min of the created date.
In Summary: Create a table with the client name and work order number which would use the first created date if the last character is either a "A" "P" or "E" or the last created date if the last character is not "A" "P" or "E"
I am able to create the expressions but am getting lost in putting them together. This is what I have so far. Not ssure where to put the If statement, and how to add the Client column to the target table.
CUSTOMER WO = SUMMARIZE(Sales, Sales[WO NUM],"CREATED DATE",CALCULATE(SUM(Sales[CREATED_TIME]),FILTER(sales,Sales[CREATED_TIME]=max(Sales[CREATED_TIME]))))
Original table is:
Sales | ||||
Bill Number | WO NUM | Client | Created Time | Last Charater |
C506550 | C506550 | XWZ | 2-Apr-17 | |
C506550 | C506550 | ABC | 6-Apr-17 | |
E478923 | E478923 | UVW | 9-May-17 | |
E478923P | E478923 | FGH | 12-May-17 | P |
E789278 | E789278 | LMN | 1-May-17 |
and new table is:
CUSTOMER WO | ||
WO NUM | Client | CREATED DATE |
C506550 | ABC | 6-Apr-17 |
E478923 | UVW | 9-May-17 |
E789278 | LMN | 1-May-17 |
Solved! Go to Solution.
This solution was a good start but the data was more complex than originally anticipated. More filters were required. The final solution was to add 2 more columns
Min Acct = if(Sales[ACCT_CODE] = CALCULATE(MIN(Sales[ACCT_CODE]),ALLEXCEPT(Sales,Sales[BILL_NUMBER],Sales[DOCUMENT_TYPE])),"yes","no")
CURRENT INV = IF(CALCULATE(MAX(Sales[DOCUMENT_TYPE]),ALLEXCEPT(Sales,Sales[WO NUM])) = "REBILL", "REBILL",iF(CALCULATE(MIN(Sales[DOCUMENT_TYPE]),ALLEXCEPT(Sales,Sales[WO NUM])) = "CREDIT","CREDIT","INVOICE"))
and then to create the table
Customer WO = SELECTCOLUMNS(filter( sales,sales[DOCUMENT_TYPE]= Sales[CURRENT INV] && Sales[CREATED_TIME] = Sales[Column] && Sales[Min Acct] = "yes" ), "WO NUMBER", Sales[WO NUM], "CLIENT", Sales[CLIENT],"CREATED TIME", Sales[CREATED_TIME])
This works except for one condition. When there is an "A", "P" or "E" bill and there is a CREDIT/REBILL invoice. Only a very small percentage of invoices fall in this category, so the solution is acceptable.
You can add a column in your original table with the below DAX:
Column = IF ( CALCULATE ( MAX ( sales[last character] ), ALLEXCEPT ( sales, sales[wo number] ) ) = "P" || CALCULATE ( MAX ( sales[last character] ), ALLEXCEPT ( sales, sales[wo number] ) ) = "A" || CALCULATE ( MAX ( sales[last character] ), ALLEXCEPT ( sales, sales[wo number] ) ) = "E", CALCULATE(MIN(sales[created time]),ALLEXCEPT(sales,sales[wo number])), CALCULATE(MAX(sales[created time]),ALLEXCEPT(sales,sales[wo number])) )
Then create a calculated table as below
[Table ] = SELECTCOLUMNS ( FILTER ( sales, sales[created time] = sales[column] ), "WO NUMBER", sales[wo number], "CLIENT", sales[client], "CREATED TIME", sales[created time] )
See more details in the attached pbix file.
This solution was a good start but the data was more complex than originally anticipated. More filters were required. The final solution was to add 2 more columns
Min Acct = if(Sales[ACCT_CODE] = CALCULATE(MIN(Sales[ACCT_CODE]),ALLEXCEPT(Sales,Sales[BILL_NUMBER],Sales[DOCUMENT_TYPE])),"yes","no")
CURRENT INV = IF(CALCULATE(MAX(Sales[DOCUMENT_TYPE]),ALLEXCEPT(Sales,Sales[WO NUM])) = "REBILL", "REBILL",iF(CALCULATE(MIN(Sales[DOCUMENT_TYPE]),ALLEXCEPT(Sales,Sales[WO NUM])) = "CREDIT","CREDIT","INVOICE"))
and then to create the table
Customer WO = SELECTCOLUMNS(filter( sales,sales[DOCUMENT_TYPE]= Sales[CURRENT INV] && Sales[CREATED_TIME] = Sales[Column] && Sales[Min Acct] = "yes" ), "WO NUMBER", Sales[WO NUM], "CLIENT", Sales[CLIENT],"CREATED TIME", Sales[CREATED_TIME])
This works except for one condition. When there is an "A", "P" or "E" bill and there is a CREDIT/REBILL invoice. Only a very small percentage of invoices fall in this category, so the solution is acceptable.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |