Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Year | Quarter | LiveP_SFInt |
2016 | Qtr 2 | 59000 |
2016 | Qtr 3 | 28832 |
2016 | Qtr 4 | 76694 |
2015 | Qtr 1 | |
2015 | Qtr 2 | |
2015 | Qtr 3 | |
2015 | Qtr 4 | 99000 |
2014 | Qtr 1 | |
2014 | Qtr 2 | |
2014 | Qtr 3 | |
2014 | Qtr 4 | 405000 |
2013 | Qtr 1 | |
2013 | Qtr 2 | |
2013 | Qtr 3 | |
2013 | Qtr 4 | 202529 |
2012 | Qtr 1 | 227467 |
2012 | Qtr 2 | 59913 |
2012 | Qtr 3 | |
2012 | Qtr 4 | 122440 |
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) )
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:
Best Regards,
Qiuyun Yu
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) )
Oh and the Column required for the above calc should be this
My Column = DATE( 'Table1'[Year], (RIGHT('Table1'[Quarter],1) * 3) - 2, 1)
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
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?
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) ?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |