Given that BUDGET is defined at the month level and ACTUAL is defined at the day level — two different levels-of-detail (LODs) — I recommend that you use Data Blending rather than row-level table joins. Data Blending allows you to aggregate before joining, which is helpful for resolving data sources that are at different LODs.
Attached is a workbook (version 10.1.4) where I attempted to a Data Blending approach. Here's what I did:
1. Bring in BW and Budget as 2 separate data sources.
2. Pivot Budget so that all the months are their own rows.
3. Create a calculated field [Join month] in both data sources. For BW, this is just DATETRUNC('month',[Calendar Day]). For Budget, this is DATEPARSE('MMM-yy',[Month Year]) — where [Month Year] is what I called the Pivot field that contains the budget months.
4. Set up [Concept] and [Join month] as Data Relationships.
5. In Budget, create a calculated field [Value to report] that pulls in SUM([Actual USD]) from BW or SUM([Budget]) from Budget, depending on whether the given month is in the past. (I assumed, also, that Budget is in 000's and multiplied appropriately, so that BUDGET and ACTUAL are in a similar range.)
6. Set up the view with Budget as the primary data source and [Value to report] as the numbers to show.
7. Filter on [Value to report] is not null, to get rid of years that (1) are in the past and (2) have no ACTUALs to report. This effectively filters out everything prior to 2017 (since there are no ACTUALs for past years), as well as any Concepts that have neither BUDGET nor ACTUAL in 2017.
Let me know if that's what you were looking to do.
sample-JC-10.1.4.twbx 57.5 KB
Jamieson, thank you very much!
It is exactly what I needed!