Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Guys
I'm having a real hard time with this and need you help. I'm trying to figure out whether playing too many games in a 5 day period affects team performance. I have a facts table SBRData that lists every game played by a team in the following format
Columns: [Date] , [ Team] , [Performance Metric1] ..... [Performance Metric 30]
What I is a column in this table that for each row, count the number of occurences for TEAM in the last 5 days from the [Date] Value in that row.
Example
Date | Team | Number of Games Last 5 Day
01/11/2017 Boston 0
02/11/2017 Boston 1
03/11/2017 Boston 2
04/11/2017 Atlanta 0
05/11/2017 Boston 3
06/11/2017 Boston 4
07/11/2017 Boston 3
Essentially my goal is to create a table w/ Number of games last 5 days in row and see all the performance metrics to determine the relationships between how rested a team is and all it's performance metrics. HELP
Number of games | Pts per 100 possesion
0 100
1 99
2 91
3 90
4 85
Solved! Go to Solution.
Hi @ArzanP, try this column
Games last 5 days = CALCULATE(COUNTROWS(SBRData);FILTER(SBRData; SBRData[Team] = EARLIER(SBRData[Team]) && SBRData[Date] <= EARLIER(SBRData[Date]) && SBRData[Date] > EARLIER(SBRData[Date]) - 5))
Hi @ArzanP,
Try using GROUP BY function in Power BI edit mode:
Onse done you can use filter selection by dropping your date field into filter options and select Relative Date Filtering and set display last 5 days:
For more information see link: Group By Function
Hope this help.
Regards
Abduvali
Hi @ArzanP, try this column
Games last 5 days = CALCULATE(COUNTROWS(SBRData);FILTER(SBRData; SBRData[Team] = EARLIER(SBRData[Team]) && SBRData[Date] <= EARLIER(SBRData[Date]) && SBRData[Date] > EARLIER(SBRData[Date]) - 5))
Mate this is perfect. I'd hug you if I could. It seems i missed the very last part of the formula 🙂
Vik you are so close. However it's not showing perfectly accurate results.
Games in Last 5 Days = CALCULATE(COUNTROWS(SBRData), FILTER(SBRData,SBRData[Team]=EARLIER(SBRData[Team])&&SBRData[Date]<=EARLIER(SBRData[Date])-5))
I used that and I'm getting values of 5/6/7 (which is literally not possible as they only play 1 game a day)
Do you think that's because the date column is not continuous (i.e It just lists the day the game was played on, and there is no reference to the days in between??) I can make another dynamic date column, and referince it. If I do that , which "Date" in the above formula do I change?
Last 5 Days = calculate(COUNTROWS(SBRData), SBRData[Date].[day] - 5)
Very interesting piece of work =D
Is this what you trying to achieve
Let me know if it works for you.
Regards
Abduvali