I have a fact table "Previous Dept Costs" that shows the total costs for each area over the previous 5 years. Columns are: "department", "year", "total cost".
I have a second table "current cost" with new data that shows individual employee costs for the current year, these are not summarised, columns "employee id", "employee cost", "employee department", "year".
im trying to achieve a visualisation to show total cost per department over time, this requires the "previous cost" from the first table and a calculated total cost for the current year from the "current cost".
I can do it by creating a calculated column in the "previous dept costs" table that calculates the sum of "current cost" but this means that current year values are not dynamic and can't be changed with a slicer (eg. To exclude an employee from the current year total).
is there any way to achieve this or am I going completely wrong trying to combine static values with calculated.
I would start with based data for previous years but this is a data set I've inherited and only summarised data has been stored 😞
thanks for any guidance
