The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have a SAP data extraction (invoices). And I need to carry out an analysis of the debt and what has been paid. My financial colleagues have given me a logic to be able to analyze that SAP data with so many fields.The model is not created yet, I only have a table that has been extracted from me.
I would like someone to help me how to use this table to create visuals of bars and lines to summarize the debt and payment.
When an invoice (ID_SAP_INVOICE) is in states (ID_STATUS_DOC_INVOICE) from 1 to 3, it is always a debt and the PENDING_NET field must be added and if it is in state 4, INVOICE_NET will be added.
Status 1 = registered
status 2 = approved
status 3 = posted
status 4 = paid
For each of the statuses, it has its own start and end date fields for the invoice in that status.
My biggest blockage is from that extracted table, which I should use to calculate the debt and payment, and the graph that they have requested of me.
Anyone helps to reach the expected end. Thank you
I attach an image of the expected result on a visual level.
The structure of the extracted table is as follows: (I attach an extraction from the year 2006 as an example)
ID_SAP_INVOICE | ID_STATUS_DOC_INVOICE | INVOICE_NET | PENDING_NET | YYYYMM_START_PEND_DEBT | YYYYMM_END_PEND_DEBT | YYYYMM_START_PENDING_DUE_DEBT | YYYYMM_END_PENDING_DUE_DEBT | YYYYMM_START_APPROVED_DEBT | YYYYMM_END_APPROVED_DEBT | YYYYMM_START_TOTAL_DUE_DEBT | YYYYMM_END_TOTAL_DUE_DEBT | YYYYMM_PAYMENT |
2006501113888 | 4 | 275,40 | 275,40 | 200611 | 200611 | |||||||
2006501103545 | 4 | 265,39 | 265,39 | 200604 | 200605 | |||||||
2006501101006 | 4 | 472,26 | 472,26 | 200602 | 200602 | |||||||
2006501101983 | 4 | 559,98 | 559,98 | 200603 | 200604 | 200606 | ||||||
2006520100109 | 4 | 262,15 | 262,15 | 200602 | 200601 | 200604 | 200601 | |||||
2006520600200 | 4 | 42751,85 | 42751,85 | 200607 | 200607 | |||||||
2006506105232 | 1 | 327,88 | 327,88 | 200611 | 200612 | 200612 | ||||||
2006501100150 | 4 | 60,48 | 60,48 | 200601 | 200601 | |||||||
2006501100192 | 4 | 268,80 | 268,80 | 200601 | 200602 | |||||||
2006520100071 | 4 | 334,30 | 334,30 | 200602 | 200601 | 200604 | 200601 | |||||
2006501117666 | 4 | 212,40 | 212,40 | 200612 | 200701 | |||||||
2006501107500 | 3 | 338,97 | 338,97 | 200606 | 200607 | 202409 | ||||||
2006520100138 | 4 | 296,72 | 296,72 | 200603 | 200602 | 200604 | 200602 | |||||
2006520100378 | 4 | 4449,81 | 4449,81 | 200605 | 200606 | |||||||
2006520600005 | 4 | 16028,33 | 16028,33 | 200602 | 200604 | 200604 | ||||||
2006506104142 | 2 | 17,42 | 17,42 | 200610 | 200701 | 200701 | ||||||
2006501103639 | 4 | 148,45 | 148,45 | 200604 | 200604 | |||||||
2006506100971 | 2 | 190,00 | 190,00 | 200604 | 200604 | 200604 | ||||||
2006501112045 | 4 | 108,70 | 108,70 | 200609 | 200609 | 200611 | 200609 | |||||
2006506105244 | 2 | 14,31 | 14,31 | 200611 | 200702 | 200702 | ||||||
2006506104143 | 2 | 18,33 | 18,33 | 200610 | 200701 | 200701 | ||||||
2006520600190 | 4 | 1243,84 | 1243,84 | 200606 | 200606 | |||||||
2006506105245 | 2 | 5,62 | 5,62 | 200611 | 200702 | 200702 | ||||||
2006506104989 | 1 | 743,73 | 743,73 | 200611 | 200702 |
Hi,@flbi365 .I am glad to help you.
according to your description, you want to display the data in a bar chart by categorizing the data in the table.
For your description, I have a few questions I would like you to answer, if you can provide a .PBIX file that does not contain sensitive data it would be very helpful to solve your problem.
1. In your data
What are the values of registered and paid shown in this visual? Do they refer to the total value of NET for each date of screening, where (registered means status=1,2,3 corresponds to the aggregated value of PENDING_NET (for each month); and paid means status=4 corresponds to the aggregated value of INVOICE_NET (for each month)).
where (registered means status=1,2,3 for PENDING_NET (for each month); and paid means status=4 for INVOICE_NET (for each month)).
Also what does total debt and expired debt mean in the discounted debt chart?
Unfortunately, I have not been able to match them successfully (I think state 2 corresponds to the two APPROVED_DEBT columns in the table, but not to the other three states).
Could you elaborate on which two date columns correspond to each of the four status codes?
3. In your data, there are nulls in every column of date data, how do you want to handle the nulls?
I think you want to summarize these 8-9 columns into a single date column (as you show in column 1), what is your merge logic and how do you want to handle null values?
Do you want to show the new date column on the X-axis in visual after finally summarizing the data from the current 8-9 columns (as you show in column 1)?
Here is my own idea based on your description:
I have created two calculated columns that show the data separately for status=1,2,3 and status=4.
Here is the DAX code:
registered = IF('Table'[ID_STATUS_DOC_INVOICE] IN {1, 2, 3}, 'Table'[PENDING_NET], BLANK())
paid = IF('Table'[ID_STATUS_DOC_INVOICE] = 4, 'Table'[INVOICE_NET], BLANK())
I would appreciate if you can answer my above query in detail and it would be better if you can give the sensitive data .PBIX file that is not included!
Looking forward to your reply.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
8 | |
8 |