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?
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