Forum Discussion

Brendan_1's avatar
Brendan_1
Advocate I
9 years ago

Summing Specific Rows by Quarters

I have a table like this: I'd like to calculate the LiveP_SFInt for the next 8 quarters for each quarter. I.e. for 2015 -Q1 it should sum up the SFInt column through 2015 - Q2 until 2017-Q2 (8 quarte...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

But here is one approach.

 

1: Add a Date table to your mode if you don't already have one.  Use this code and adjust dates as needed 

 

Dates = CALENDAR(DATE(2012,1,1) , DATE(2018,1,1))

2: Add this column to your data table and create a relationship to your Date table.  This creates a date for each quarter.

 

My Column = DATE(
                'Table1'[Year],
                RIGHT('Table1'[Quarter],1),
                1)

Then create this measure

 

Measure = CALCULATE(
					SUM('Table1'[LiveP_SFInt]),
					DATESINPERIOD(
						'Dates'[Date],
						FIRSTDATE('Table1'[My Column])
						,8,
						QUARTER)
					)
Brendan_1's avatar
Brendan_1
Advocate I
9 years ago

I do have a date column in the data already. It is a full date and the column name is LiveP_ConstructionCompletedDate

 

When i try your formula i have the below but i get an error message that the Syntax for "," is incorrect.

 

Under Construction Calc = SUM('Building Live Data'[LiveP_SFInt])
,DATESINPERIOD('Building Data'[ConstructionCompletedDate]
,FIRSTDATE('Building Data'[ConstructionCompletedDate])
,8,QUARTER))

 

Any idea?