Issue with refund calculation per month

Anonymous — Mon, 04 Oct 2021 12:35:53 GMT

Hi all,

I'm pretty new to power bi. I need to calculate some refunds for my boss, but can't get the right calculation.

I have a table (Sheet1) with billing type (credit(refund) and invoice(turnover). This is the current formula, but this one calculates it wrong, because now all payed refunds will be divived by turnover that month. But the refunds can be from an order before.

So when July has a turnover of 1000 and August of 500 and the credits are 200, then it will say 200/500 = 40% refund. For example 150 is from July and 50 from August. This will mean 150/1000 = 15% from July in August and 50/500 = 10% in August from August = 25% refund August. This can apply for up to 12 months backwards. The fields of the occurring month is "Billing year" and "Billing month". The field of which month the order is, is called "Hulp". Please share your insights with me

Refund% = (CALCULATE(sum(Sheet1[EUR sales]),FILTER(Sheet1, Sheet1[Billing type]="Credit Memo")))/CALCULATE(sum(Sheet1[EUR sales]),FILTER(Sheet1, Sheet1[Billing type]="Invoice"))*-1

Company code	Continent	Sales account	Billing type	Hulpveld billing date	Billing year	Billing month	EUR sales
2000	Europe	B2B	Invoice	202109	2021	9	3000
2000	Europe	B2B	Invoice	202108	2021	8	4000
2000	Europe	B2B	Invoice	202107	2021	7	5000
2000	Europe	B2B	Invoice	202106	2021	6	7500
2000	Europe	B2B	Credit memo	202109	2021	9	150
2000	Europe	B2B	Credit memo	202108	2021	9	100
2000	Europe	B2B	Credit memo	202107	2021	9	250
2000	Europe	B2B	Credit memo	202106	2021	9	250
2000	Europe	B2B	Credit memo	202108	2021	8	200
2000	Europe	B2B	Credit memo	202107	2021	8	100
2000	Europe	B2B	Credit memo	202106	2021	8	300
2000	Europe	B2B	Credit memo	202107	2021	7	100
2000	Europe	B2B	Credit memo	202106	2021	7	500

Expected outcome	july	august	september
202109			5,00%
202108		5,00%	2,50%
202107	2,00%	2,00%	5,00%
202106	6,67%	4,00%	3,33%
Refund%	8,67%	11,00%	15,83%

Re: Issue with refund calculation per month

Anonymous — Thu, 07 Oct 2021 14:30:29 GMT

Am I allowed to bump this, because first it was unjustly marked as spam.

Re: Issue with refund calculation per month

smpa01 — Thu, 07 Oct 2021 16:12:34 GMT

Anonymous please try

Refund% = VAR _credit = CALCULATE ( SUM ( Sheet1[EUR sales] ), FILTER ( VALUES ( Sheet1[Billing type] ), Sheet1[Billing type] = "Credit Memo" ) ) VAR _invoice = CALCULATE ( SUM ( Sheet1[EUR sales] ), ALL ( Sheet1[Billing month] ), Sheet1[Billing type] IN { "Invoice" } ) RETURN DIVIDE ( _credit, _invoice )

Re: Issue with refund calculation per month

Anonymous — Tue, 12 Oct 2021 06:57:27 GMT

Unfortunatly I didn't receive a different result (Refund %2 = your formula)

Re: Issue with refund calculation per month

Anonymous — Mon, 06 Dec 2021 11:17:35 GMT

Bump

topic Re: Issue with refund calculation per month in DAX Commands and Tips

Issue with refund calculation per month

Re: Issue with refund calculation per month

Re: Issue with refund calculation per month

Re: Issue with refund calculation per month

Re: Issue with refund calculation per month