Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Issue with refund calculation per month

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 codeContinentSales accountBilling typeHulpveld billing dateBilling yearBilling monthEUR sales
2000EuropeB2BInvoice202109202193000
2000EuropeB2BInvoice202108202184000
2000EuropeB2BInvoice202107202175000
2000EuropeB2BInvoice202106202167500
2000EuropeB2BCredit memo20210920219150
2000EuropeB2BCredit memo20210820219100
2000EuropeB2BCredit memo20210720219250
2000EuropeB2BCredit memo20210620219250
2000EuropeB2BCredit memo20210820218200
2000EuropeB2BCredit memo20210720218100
2000EuropeB2BCredit memo20210620218300
2000EuropeB2BCredit memo20210720217100
2000EuropeB2BCredit memo20210620217500

 

Expected outcomejulyaugustseptember
202109  5,00%
202108 5,00%2,50%
2021072,00%2,00%5,00%
2021066,67%4,00%3,33%
Refund%8,67%11,00%15,83%

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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 )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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