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?

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi Brendan_1,

     

    You lost Calculate() function in your measure. Please double check Brendan_1's measure:

     

     

    Best Regards,
    Qiuyun Yu

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      Sorry, I think I had a typo in the formula

       

      Measure = CALCULATE(
		SUM('Table1'[LiveP_SFInt]),
		DATESINPERIOD(
		Table1[My Column],
		FIRSTDATE('Table1'[My Column])
		,8,
		QUARTER)
		)
      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Oh and the Column required for the above calc should be this

         

        My Column = DATE(
                'Table1'[Year],
                (RIGHT('Table1'[Quarter],1) * 3) - 2,
                1)