Forum Discussion
Cumulative Sum
Hello,
I have one table with [Date], [Capacity Need], [Production Order]
related with the [Date Dimension Table] with the field [Date], [Year], [WeekOfYear], [LastDateOfPreviousWeek] ...,
i need a Measure for the Expired Prdoduction Order doing the sum of [Capacity Need] between the MIN date in [Date Dimension] and the [LastDayOfPreviousWeek], i have some problem with the FILTER in the CALCULATE formula.
Any suggestions? Thanks!
Solved,
I added two columns in DateDimension, one with the first day of the week,
and one that concatenates the year of the first day of the week with the number of the week "ISO", this because it can happen that in some years there are two weeks Nr. 52, as in the case where the last week of the year is between two years;
finally I added this measure:ManAllocHoursRunningSum =
CALCULATE(
SUM(
'Capacity Need'[Man Allocated Hours]);
FILTER(
ALL(DateDimension[Date]);
DateDimension[Date]<=MIN(DateDimension[LastDayOfPreviousWeek]
)
)
)now it works well
4 Replies
Seems like it would be something like:
Measure = CALCULATE(SUM(Table[Capacity Need]),FILTER(Table[Date] >= MIN('Date Dimension Table'[Date]) && Table[Date] <= MAX('Date Dimension Table'[LastDayOfPreviousWeek])))Assuming lastdayofpreviousweek is a date.
Maybe I did not say it clear enough
this is an exmple of data:
Capacity Need (Table) Department Date Capacity Need Prod. Order DEPT1 15/01/2018 10,00 P1 DEPT1 16/01/2018 10,00 P2 DEPT1 17/01/2018 10,00 P3 DEPT1 18/01/2018 10,00 P4 DEPT1 19/01/2018 10,00 P5 DEPT1 20/01/2018 10,00 P6 DEPT1 21/01/2018 10,00 P7 DEPT1 22/01/2018 10,00 P8 DEPT1 23/01/2018 10,00 P9 DEPT1 24/01/2018 10,00 P10 DEPT1 25/01/2018 10,00 P11 DEPT1 26/01/2018 10,00 P12 DEPT1 27/01/2018 10,00 P13 DEPT1 28/01/2018 10,00 P14 DEPT2 15/01/2018 10,00 P15 DEPT2 16/01/2018 10,00 P16 DEPT2 17/01/2018 10,00 P17 DEPT2 18/01/2018 10,00 P18 DEPT2 19/01/2018 10,00 P19 DEPT2 20/01/2018 10,00 P20 DEPT2 21/01/2018 10,00 P21 DEPT2 22/01/2018 10,00 P22 DEPT2 23/01/2018 10,00 P23 DEPT2 24/01/2018 10,00 P24 DEPT2 25/01/2018 10,00 P25 DEPT2 26/01/2018 10,00 P26 DEPT2 27/01/2018 10,00 P27 DEPT2 28/01/2018 10,00 P28 Date Dimension (Table) Date WeekNumber LastDayOfPreviousWeek 15/01/2018 3 14/01/2018 16/01/2018 3 14/01/2018 17/01/2018 3 14/01/2018 18/01/2018 3 14/01/2018 19/01/2018 3 14/01/2018 20/01/2018 3 14/01/2018 21/01/2018 3 14/01/2018 22/01/2018 3 21/01/2018 23/01/2018 3 21/01/2018 24/01/2018 3 21/01/2018 25/01/2018 3 21/01/2018 26/01/2018 3 21/01/2018 27/01/2018 3 21/01/2018 28/01/2018 3 21/01/2018 15/01/2018 4 14/01/2018 16/01/2018 4 14/01/2018 17/01/2018 4 14/01/2018 18/01/2018 4 14/01/2018 19/01/2018 4 14/01/2018 20/01/2018 4 14/01/2018 21/01/2018 4 14/01/2018 22/01/2018 4 21/01/2018 23/01/2018 4 21/01/2018 24/01/2018 4 21/01/2018 25/01/2018 4 21/01/2018 26/01/2018 4 21/01/2018 27/01/2018 4 21/01/2018 28/01/2018 4 21/01/2018 this is the pivot I would like to get
WeekNumber 3 3 4 4 Expired Week Hours Expired Week Hours DEPT1 0 70 70 70 DEPT2 0 70 70 70 Many Thanks in advance
Lorenzo
Solved,
I added two columns in DateDimension, one with the first day of the week,
and one that concatenates the year of the first day of the week with the number of the week "ISO", this because it can happen that in some years there are two weeks Nr. 52, as in the case where the last week of the year is between two years;
finally I added this measure:ManAllocHoursRunningSum =
CALCULATE(
SUM(
'Capacity Need'[Man Allocated Hours]);
FILTER(
ALL(DateDimension[Date]);
DateDimension[Date]<=MIN(DateDimension[LastDayOfPreviousWeek]
)
)
)now it works well
