Forum Discussion

Brendan_1's avatar
Brendan_1
Icon for Advocate I rankAdvocate 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 quarters). Any idea how to do this?

 

YearQuarterLiveP_SFInt
2016Qtr 259000
2016Qtr 328832
2016Qtr 476694
2015Qtr 1 
2015Qtr 2 
2015Qtr 3 
2015Qtr 499000
2014Qtr 1 
2014Qtr 2 
2014Qtr 3 
2014Qtr 4405000
2013Qtr 1 
2013Qtr 2 
2013Qtr 3 
2013Qtr 4202529
2012Qtr 1227467
2012Qtr 259913
2012Qtr 3 
2012Qtr 4122440
Need Help

9 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Do you have a date column in your data?  If you do you can take advantage of the Time Intelligence functions built into Power BI.

     

    Otherwise I take it in your data that Qtr 1, of 2015 starts on Jan 1st 2015 (and is not a financial year) ?

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft 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
      Icon for Advocate I rankAdvocate 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?