Need Advice on Approach: Producing "... to date" comparisons when data granularity is mixed
Ok... this is a mind bender for me, and maybe that's because I'm too close to it... (forest for the trees). Hoping the Dojo can come to the rescue and point me in the right direction.
Here's the business question: How can I see how our sales are performing to plan and vs prior year? (YTD, MTD - and including vs the same day last year)
- I have raw (uncorrected) sales transactional data with daily granulatiry (current year, prior year +)
- I have official financial sales data from accounting (any issues corrected) with month granulatity (plan & actual)
When to use which data: Once a month is closed by accounting we want to use the official financial data, broken down to a weekly grain. I am doing this now by dividing the total monthly values by the weeks in the accounting period (5-4-4). Thinking I need to next break it down to daily grain? If the month is not yet closed by accounting we want to use the raw sales data. I have a comparison in my model now that checks on which period is closed and brings in the data from each source as I described. That part is fairly straight forward.
I currently have a page and cards that are showing our performance "This Month" and "This Year", but when we start a new period the comparison is skewed because the history is a full weeks worth of data vs maybe only a couple of days. The gap closes as the days progress in the data. For example: today there is data through Thursday's invoicing compared to a full week of plan/prior year data. Saturday morning the data is apples to apples from a number of days standpoint. Our leadership wants to see comparable values without that skewed view... hence the need for daily granularity.
Am I making sense?
@jaeW_at_Onyx maybe a topic for a video?
- 10.7K All Categories
- 13 Getting Started in the Community
- 41 Beastmode & Analytics
- 2.1K Data Platform & Data Science
- 59 Domo Everywhere
- 2.7K Charting
- 2.5K Ideas Exchange
- 1.3K Connectors
- 362 Workbench
- 303 Use Cases & Best Practices
- 503 APIs
- 120 Apps
- 48 News
- 753 Onboarding
- 1.2K 日本支部
- Private Company Board