Skip to main content

My shop takes Credit Card payments and the % fees depend on monthly sales volumes.

I have built a KPI Sheet, which gives me the cost per transaction including the credit card fees.

 

What I would like to do is the following:

calculate the fixed Volume for any given month which is then used to calculate the percentage Fees.

 

I am already doing this in a monthly view where I basically calculate the days completed in that month, and then extrapolate my numbers for the full month. i.e. on day 15 in a 30 day month, I would calculate "current volume x 2" for the full month.

 

Once I switch to Daily or Weekly, those volumes don't work anymore, because I keep multiplying the period's volume by 2, Instead of taking the total volume of 15 days again...

 

Here are the forumlas I am using:

 

To calculate the days in any full month:

AVG(DAY(DATEADD('day',-1,DATEADD('month',1,DATETRUNC('month',[created])))))

 

To calculate the days completed in a given month:

If MONTH([created]) = MONTH(TODAY()) AND YEAR([created]) = YEAR(TODAY()) THEN DAY(TODAY()-1)

ELSE 

DAY(DATEADD('day',-1,DATEADD('month',1,DATETRUNC('month',[created])))) 

END

 

Calculate the multiplier of the actual volume:

1/(AVG([Days Completed in given Month])/[Days in full Month])

 

To calculate the full month applicable Orders

[Sales]* [Full Month Multiplier]

 

How do I "not" calculate that Sales Volume for the selected period but for the days completed in the respective month?

6 respuestas
  1. 21 dic 2022, 13:13

    Thanks - but I would like to see how you approached the problem and the loss you created

    you can look to https://jimdehner.com/2022/09/06/calculating-averages-or-rates-how-to/ on calculating averages or rates - several examples including some using lods

    When you have your workbook with fake date and your approach available please return and post it here as a twbx

    Thanks

    Jim - Tableau Visionary

0/9000