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.
Hi I have a dataset like this, the date difference between each sprint is 14 days.
Team DateBegin Sprint Points Team1 1/2/2021 Sprint1 30 Team1 1/16/2021 Sprint2 40 Team1 1/30/2021 Sprint3 20 Team1 2/13/2021 Sprint4 70 Team1 2/27/2021 Sprint5 90 Team1 3/11/2021 Sprint6 55 Team1 3/25/2021 Sprint7 40 Team2 1/2/2021 Sprint1 30 Team2 1/16/2021 Sprint2 40 Team2 1/30/2021 Sprint3 20 Team2 2/13/2021 Sprint4 70 Team2 2/27/2021 Sprint5 90 Team2 3/11/2021 Sprint6 55 Team2 3/25/2021 Sprint7 40
I wanted to calculate the Sum average using the last 3 sprints. for example
for Sprint7- sum(Sprint6+Sprint5+Sprint4)/3 For Sprint6- sum(Sprint5+Sprint4+Sprint3)/3
Something like For each Sprint look up the last 3 sprints and Sum them up and take the average(measure).
I have tried using the below dax and the rollup average works fine with out the "Team" column. How can I use the Team column to as a group by..something like the rollup calculation should start all over again for different teams.
Last3Avg = VAR ThisSprintStart = SELECTEDVALUE ( Sprints[DateBegin] ) VAR SprintsToDate = FILTER ( ALLSELECTED ( Sprints ), Sprints[DateBegin] < ThisSprintStart ) RETURN AVERAGEX ( TOPN ( 3, SprintsToDate, Sprints[DateBegin] ), [Points] )
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
First you need to create index column by group:
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Team"}, {{"Count", each _, type table [Team=nullable text, DateBegin=nullable date, Sprint=nullable text, Points=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"index",1)),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Team", "Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns2", "Custom", {"Team", "DateBegin", "Sprint", "Points", "index"}, {"Custom.Team", "Custom.DateBegin", "Custom.Sprint", "Custom.Points", "Custom.index"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Team", "Team"}, {"Custom.DateBegin", "DateBegin"}, {"Custom.Sprint", "Sprint"}, {"Custom.Points", "Points"}, {"Custom.index", "index"}})
Then you can use the following measure:
Measure = MAX('Table'[Points])- AVERAGEX(TOPN(3,FILTER(ALLEXCEPT('Table','Table'[Team]),'Table'[index] <MAX('Table'[index])),'Table'[index],DESC),'Table'[Points])
Please refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Anonymous , Create a rank on sprint begindate ( create a separate table with sprint and begin date)
sprint rank = RANKX(all('sprint '),'Dsprint ate'[begindate ],,ASC,Dense)
measures
Last 3 sprint = CALCULATE(sum('Table'[points]), FILTER(ALL('sprint '),'sprint '[sprint Rank]>=max('sprint '[sprint Rank])-3 && 'sprint '[sprint Rank]<=max('sprint '[sprint Rank])))
I did try it by correcting the spell errors but it did not bring the right result finally for the last 3 sprints🙂
Hi @Anonymous ,
First you need to create index column by group:
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Team"}, {{"Count", each _, type table [Team=nullable text, DateBegin=nullable date, Sprint=nullable text, Points=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"index",1)),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Team", "Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns2", "Custom", {"Team", "DateBegin", "Sprint", "Points", "index"}, {"Custom.Team", "Custom.DateBegin", "Custom.Sprint", "Custom.Points", "Custom.index"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Team", "Team"}, {"Custom.DateBegin", "DateBegin"}, {"Custom.Sprint", "Sprint"}, {"Custom.Points", "Points"}, {"Custom.index", "index"}})
Then you can use the following measure:
Measure = MAX('Table'[Points])- AVERAGEX(TOPN(3,FILTER(ALLEXCEPT('Table','Table'[Team]),'Table'[index] <MAX('Table'[index])),'Table'[index],DESC),'Table'[Points])
Please refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Sorry, I do not see my comment so I am just writing back again if my first comment did not went through. Is the Rank formula have spelling errors. Do you mean to write it like this.
sprint rank = RANKX(all('sprint '),'sprint'[begindate ],,ASC,Dense)
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 |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
89 | |
66 |