After attending TC18, I have been tasked to transition ALL my previous Excel work into Tableau dashboards - I'm very excited, but a very overwhelmed!
I have attached a sample of my Excel work for your reference. I believe I have two issues I need to resolve:
1) On the attached Excel file, I'm trying to calculate the Annual Recurring Revenue (ARR) for each customer (column L). I have the formula worked out in Excel; however, I'm having an issue with this in Tableau.
Below I explain how I came up with the formulas in Excel; however, should I just import my excel sheet to Tableau with the formulas already calculated and made available in a measure??
The formula is Master Start Date (column BH) - Master End date (BI) this = Contract duration (column (BJ)....this is simple. I then take the Recurring ACV - Annual (column BMW) and divide this by the Contract duration to get the Daily subscription fee (column BK)....again very easy even I can do this in Tableau. Here is the hard part - I then take the Daily subscription fee and spread the revenue across each month. Since my Master start date (column BH) = Feb 23, 2018, I can recognize 6 days of revenue in Feb 2018 (Feb23rd - Feb 28). So I then take 6* my daily subscription fee and allocate $65.96 for Feb. Since there are 31 days in March 2018, I can recognize $340.77 in March (31*Daily subscription fee),......this continues until I reach my Master End Date (column BI). I have been able to create this (as you can see in Excel; however, I'm really struggling to replicate in 10.4.
2) My next problem is I need to be able to aggregate the total ARR for each month (e.g. Feb 18 has =$181.31 of revenue). THis again is simple; however, in the same dashboard, I need to take my Cal.year/month and find the sum of EURO Conversion (Column AQ). I want to have ONE parameter that handles both date fields in Tableau. Is that possible?
If you made it this far, CHEERS!
All help and suggestions are appreciated
Sample data.xlsx 173.8 KB