I have a table that breaks down month by month my data, when doing pure counts the data calculates correctly. However when I calculate the costs I have to split between 2 costs dependant on my workstream and work out the total.
As my data has multiple date columns I loaded in an inline table like below;
LOAD * Inline [
Month Begin , Month End, Month Title
01/01/2022 , 31/01/2022 , Jan 2022
01/02/2022 , 28/02/2022 , Feb 2022
01/03/2022 , 31/03/2022 , Mar 2022
01/04/2022 , 30/04/2022 , Apr 2022
01/05/2022 , 31/05/2022 , May 2022
01/06/2022 , 30/06/2022 , Jun 2022
01/07/2022 , 31/07/2022 , Jul 2022
01/08/2022 , 31/08/2022 , Aug 2022
01/09/2022 , 30/09/2022 , Sep 2022
01/10/2022 , 31/10/2022 , Oct 2022
01/11/2022 , 30/11/2022 , Nov 2022
01/12/2022 , 31/12/2022 , Dec 2022
];
I used this as a base to link my dates to, as above the count calculations function and I am unsure if I am overcomplicating things; I used the calculation below for one of my costs, the others are very similar, I achieve the correct value for my autocalc Total on the Pivot table but all of the costs appear only in January,
e.g In January we received 50 total and in February we received 60 total, the cost should be split between them in the table and not all appear in the january column with £0.00 in the February column.
=sum(aggr((if([Workstream]<>'Audi' AND [Workstream]<>'Mercedes' AND [Workstream]<>'BMW',
count(if([Received Date]>=[Month Begin] and [Received Date]<=[Month End],[Received Date]))*52.5,
count(if([Received Date]>=[Month Begin] and [Received Date]<=[Month End],[Received Date]))*47.5)),[Workstream]))
Is the calculation I'm carrying out incorrect or would it be more efficient to just input a '2022 Total' in the Inline table?
