cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
calerof
Solution Supplier
Solution Supplier

how to get net sales from two tables, a) Invoices and b) Credit notes

Hello Community,

I'm working with SAP B1.

I'm struggling to get the detailed net sales by customer from two tables:

  1. Detial Invoices by line item (INV1)
  2. Detail Credit notes by line item (RIN1)

These two tables relate to the calendar table and to the total invoice table (OINV) and total credit note table (ORIN). Also, to several other master data, i.e. customer code, item code.

 

When I apply the following code I get the total net sales correctly as one value:

Sales YTD = IF(
                MIN( 'Calendar'[Date] ) <= CALCULATE( MAX( OINV[DocDate] ), ALL( OINV ) ),
                CALCULATE(
                        SUMX(INV1, INV1[Subtotal MXP] )
                        -
                        SUMX(RIN1, RIN1[Subtotal MXP NCred]),
                        DATESYTD( 'Calendar'[Date] )
                )
) 

I also attempted the following variance in the code above and it worked fine for the total net sales as one value:

 

Sales YTD = IF(
                MIN( 'Calendar'[Date] ) <= CALCULATE( MAX( OINV[DocDate] ), ALL( OINV ) ),
                CALCULATE(
                        OINV[Net Sales],
                        DATESYTD( 'Calendar'[Date] )
                )
) 

where:

 

Net Sales = INV1[Subtotal MXP] - RIN1[Subtotal MXP NCred]

and:

 

Subtotal MXP = SUM( INV1[Line Total] )
Subtotal MXP NCred = SUM( RIN1[Line Total] )

The problem comes when I try to create a matrix/chart with net sales by customer, my result looks like this:

 

Bottom left chart with errorBottom left chart with error

Any help would be greatly appreciated.

 

Kind regards,

 

Fernando

 

1 ACCEPTED SOLUTION

Hello Xiaoxin,

 

Thank you very much for your message. I managed to fix or rather overcome this issue by doing two things:

  1. Merged the columns missing in the detailed tables, i.e. OINV and ORIN into INV1 and RIN1, so I had all required information in two tables instead of four.
  2. Created a new table with UNION and SUMMARIZEDCOLUMNS to bring together INV1 and RIN1's required columns.
  3. Finally, in the edit queries mode, Transform, Number Column, I multiplied the credit notes's quantity and amount (LineTotal) by -1, so I could just SUM their values.

Learning but always feeling supported by the community.

 

Thanks!

 

Fernando

 

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @calerof,

 

Can you please share a pbix file for test?

 

BTW, nested measures not fully works on summary level, it will drill and calculate on detail records and summary result instead calculate with summary records.

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello Xiaoxin,

 

Thank you very much for your message. I managed to fix or rather overcome this issue by doing two things:

  1. Merged the columns missing in the detailed tables, i.e. OINV and ORIN into INV1 and RIN1, so I had all required information in two tables instead of four.
  2. Created a new table with UNION and SUMMARIZEDCOLUMNS to bring together INV1 and RIN1's required columns.
  3. Finally, in the edit queries mode, Transform, Number Column, I multiplied the credit notes's quantity and amount (LineTotal) by -1, so I could just SUM their values.

Learning but always feeling supported by the community.

 

Thanks!

 

Fernando

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!