Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Community,
I'm working with SAP B1.
I'm struggling to get the detailed net sales by customer from two tables:
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:
Any help would be greatly appreciated.
Kind regards,
Fernando
Solved! Go to Solution.
Hello Xiaoxin,
Thank you very much for your message. I managed to fix or rather overcome this issue by doing two things:
Learning but always feeling supported by the community.
Thanks!
Fernando
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
Hello Xiaoxin,
Thank you very much for your message. I managed to fix or rather overcome this issue by doing two things:
Learning but always feeling supported by the community.
Thanks!
Fernando