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

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)

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Did you see my updates?  I had some errors in the first post

    • Brendan_1's avatar
      Brendan_1
      Advocate I
      9 years ago

      Yeah, i saw your edits and included them, it still only sums up the SF under construction for each specific quarter. 

       

      I believe my formulas are the same as yours except i name My Column - "Column for Under Construction"

       

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

       

      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))

       

      Did i miss anything?