First of all, great content!
Here’s the problem: I am trying to calculate something similar to your “cumulative actual vs budget comparison”-tutorial but with a slightly different setup. Basically, my entities are projects (eg. rather than products) with fixed starting and ending dates.
The budget is in a fact-table with project-level granularity. Fx. project A: 1,000 in various costs, project B: 5,000 in various costs, etc. The actuals are in another fact table with day-by-day accounting entries. This is all linked by a project look up table containing the starting and ending dates of each project. Further, a custom date table is linked to ending dates (for non-related reasons) but with an inactive relationship with the posting date in the actuals table. (see screen dump which is in danish but the table names should be translateable)
I’d like to create an overview that allows the user to filter on a single project in order to show a comparison between budget and actual costs displayed in a line/area chart with months on the x-axis. When no project filter is applied it would be awesome to see the comparison in totals but I don’t know if this is possible.
I’ve tried every way of applying your logic of “days in context / days in year x budget” but the denominator obviously has to express the number of days between starting and ending date of the project and I just can’t get the syntax right.
Any help or pointers towards similar issues is much appreciated! Let me know if additional information is needed.
Thanks in advance.
Edit: projects are linked by a unique project ID.