Forum Discussion
Calculate column with subtract rows
HI,
I want to calculate the column and subtract the rows.
the columns are this http://prntscr.com/o8ldj1
and it should be like this: http://prntscr.com/o8lfm5
Thanks
Ok, this should work. It is a measure as well. Your title was a bit confusing as you asked for a calculate column.
FloatingStart = VAR SelectedYear = YEAR( SELECTEDVALUE( Table1[Start Date] ) ) VAR SelectedMonth = MONTH( SELECTEDVALUE( Table1[Start Date] )) VAR FloatingStart = CALCULATE( SUM( Table1[Floating Start] ), ALL( Table1 ), YEAR( Table1[Start Date] ) = SelectedYear, MONTH( Table1[Start Date] ) <= 13 - SelectedMonth ) RETURN FloatingStart
12 Replies
I am a little confused by your screenshots as your expected table seems to have the dates sorted in the wrong direction. If the SUM's should go in order (ie, 2/1/18 = 1/1/18 + 2/1/18) this will do the job:
Rolling Start = CALCULATE( SUM( Table1[Floating Start] ), ALL( Table1[Start Date] ), FILTER( Table1, ( Table1[Start Date] ) <= EARLIER( Table1[Start Date] )) )HI,
The start date is all the months in the year 2018.
I want to create a measure - not a column.
the calc in the second screenshot explains how it should be. (2/1/18 = the second calc in the pic)
Regards
Ok, this should work. It is a measure as well. Your title was a bit confusing as you asked for a calculate column.
FloatingStart = VAR SelectedYear = YEAR( SELECTEDVALUE( Table1[Start Date] ) ) VAR SelectedMonth = MONTH( SELECTEDVALUE( Table1[Start Date] )) VAR FloatingStart = CALCULATE( SUM( Table1[Floating Start] ), ALL( Table1 ), YEAR( Table1[Start Date] ) = SelectedYear, MONTH( Table1[Start Date] ) <= 13 - SelectedMonth ) RETURN FloatingStart
