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
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)
      • Brendan_1's avatar
        Brendan_1
        Advocate I
        9 years ago

        Phil,

         

        Thanks for all your help. The formulas are calculating but it is returning the buildings that were just built in that quarter. It isn't adding the next eight quarters together. Here are my formulas. Am I doing something wrong?

         

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

         

        Column for Under Construction = DATE('Building Live Data'[LiveP_ConstructionCompletedDate].[Year],(RIGHT('Building Live Data'[LiveP_ConstructionCompletedDate].[Quarter],1)*3)-2,1)