Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Brendan_1
Frequent Visitor

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
9 REPLIES 9
Phil_Seamark
Employee
Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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?

Hi @Brendan_1,

 

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

 

q1.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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)

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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?

Phil_Seamark
Employee
Employee

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.