Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have data that looks like the image below (the first 3 columns).
I am trying to creat a calculation that will sum over a rolling 6 terms... see "Rolling" column. Each value in the Rolling column will be a sum for that row + the previous 5 rows, so 6 rows total.
There has to be a way to achieve this with a measure, but I can't seem to figure it out.
Thanks so much!
@heidibb try following
Rolling 6 rows =
VAR __currRow = MAX ( Table[TermAxis] )
VAR __startRow = _currRow - 5
RETURN
CALCULATE ( SUM ( Table[Value] ), Table[TermAxis] >= __startRow, Table[TermAxis] <= __currRow)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you! It's not quite working.
I created a calculated measure using this per your example:
The output for this is just the same value as the individual term value vs. the sum of the 6 terms
@heidibb interesting, I just tested at my end and it is working fine, not sure what we are missing here.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@heidibb do you have any other filters, a relationship which will be causing this, for testing, add ALL ( TableName ) and see if it work, although it is not required.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Yes, there are other filters. Good catch! All didn't work, but ALLSELECTED did:
THANK YOU!!!
@parry2k -- one additional question for you on this cacluation. It's working great, but one additional layer of complexity.. The rolling window for undergraduate terms is 6 terms and the rolling window for graduate terms is 5 terms. I am hoping to have one calculation that does this rolling metric figuring in the different rolling windows. We do have a way to determine the term type of UG and GR, so that can be used as an attribute somewhere.
I thought I could fumble my way through it, but I'm hitting a wall. I am wondering if there is a way to adjust the logic in the __StartRow using the term type criteria?
As a reminder, the current calcuation is:
Just to close the loop on this, I think I've figured it out.
I created a UG fee:
Rolling Graduated 150% UG ?
VAR __currRow máx ( 'Graduation Retention'[DegreeLevelCohortTermAxis])
VAR __startRow - __currRow - 5
return
CALCULATE ([Total Graduate 150%], ALLSELECTED('Graduation Retention'), 'Academic Program'[AcademicLevelCode] - "UG", 'Graduation Retention'[DegreeLevelCohortTermAxis] >-__startRow , 'Graduation Retention'[DegreeLevelCohortTermAxis] <-__currRow)
And a GR rate:
Rolling Graduate 150% GR ?
VAR __currRow máx ( 'Graduation Retention'[DegreeLevelCohortTermAxis])
VAR __startRow - __currRow - 4
return
CALCULATE ([Total Graduate 150%], ALLSELECTED('Graduation Retention'), 'Academic Program'[AcademicLevelCode] - "GR",'Graduation Retention'[DegreeLevelCohortTermAxis] > __startRow , 'Graduation Retention'[DegreeLevelCohortTermAxis] <-__currRow)
Here's a final measure:
150% - SWITCH(SELECTEDVALUE('Graduation Retention'[Academic Level Code]), "UG", [Rolling Graduated 150% UG] , "GR", [Rolling Graduated 150% GR])
Just to close the loop on this, I think I've figured it out.
I created a UG rate:
Rolling Graduated 150% UG =
VAR __currRow = max ( 'Graduation Retention'[DegreeLevelCohortTermAxis])
VAR __startRow = __currRow - 5
RETURN
CALCULATE ([Total Graduated 150%], ALLSELECTED('Graduation Retention'), 'Academic Program'[AcademicLevelCode] = "UG", 'Graduation Retention'[DegreeLevelCohortTermAxis] >= __startRow , 'Graduation Retention'[DegreeLevelCohortTermAxis] <= __currRow)
And a GR rate:
Rolling Graduated 150% GR =
VAR __currRow = max ( 'Graduation Retention'[DegreeLevelCohortTermAxis])
VAR __startRow = __currRow - 4
RETURN
CALCULATE ([Total Graduated 150%], ALLSELECTED('Graduation Retention'), 'Academic Program'[AcademicLevelCode] = "GR",'Graduation Retention'[DegreeLevelCohortTermAxis] >= __startRow , 'Graduation Retention'[DegreeLevelCohortTermAxis] <= __currRow)
Then a final measure:
150% = SWITCH(SELECTEDVALUE('Graduation Retention'[Academic Level Code]), "UG", [Rolling Graduated 150% UG] , "GR", [Rolling Graduated 150% GR])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |