Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
flbi365
Regular Visitor

Sum of debt/paid conditional on 8-9 date fields

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.

flbi365_0-1718971718077.png

The structure of the extracted table is as follows: (I attach an extraction from the year 2006 as an example)

ID_SAP_INVOICEID_STATUS_DOC_INVOICEINVOICE_NETPENDING_NETYYYYMM_START_PEND_DEBTYYYYMM_END_PEND_DEBTYYYYMM_START_PENDING_DUE_DEBTYYYYMM_END_PENDING_DUE_DEBTYYYYMM_START_APPROVED_DEBTYYYYMM_END_APPROVED_DEBTYYYYMM_START_TOTAL_DUE_DEBTYYYYMM_END_TOTAL_DUE_DEBTYYYYMM_PAYMENT
20065011138884275,40275,40    200611 200611  
20065011035454265,39265,39    200604 200605  
20065011010064472,26472,26    200602 200602  
20065011019834559,98559,98200603   200604 200606  
20065201001094262,15262,15200602 200601 200604 200601  
2006520600200442751,8542751,85    200607 200607  
20065061052321327,88327,88200611 200612   200612  
2006501100150460,4860,48    200601 200601  
20065011001924268,80268,80    200601 200602  
20065201000714334,30334,30200602 200601 200604 200601  
20065011176664212,40212,40    200612 200701  
20065011075003338,97338,97200606   200607 202409  
20065201001384296,72296,72200603 200602 200604 200602  
200652010037844449,814449,81    200605 200606  
2006520600005416028,3316028,33200602   200604 200604  
2006506104142217,4217,42200610 200701   200701  
20065011036394148,45148,45    200604 200604  
20065061009712190,00190,00200604 200604   200604  
20065011120454108,70108,70200609 200609 200611 200609  
2006506105244214,3114,31200611 200702   200702  
2006506104143218,3318,33200610 200701   200701  
200652060019041243,841243,84    200606 200606  
200650610524525,625,62200611 200702   200702  
20065061049891743,73743,73200611 200702      
1 REPLY 1
Anonymous
Not applicable

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

vjtianmsft_0-1719200280188.png

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?

  1. Is there a one-to-one correspondence between the four states and the time columns in the data you gave me, each state has two columns of date data (corresponding to the start date and the end date), as you mentioned in your description?

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())

 

vjtianmsft_1-1719200341075.png

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.