How to count number of games in the last 5 days for a sports database

[ArzanP]

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

[vik0810 · Accepted 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))

[Abduvali]

Hi @ArzanP,

Try using GROUP BY function in Power BI edit mode:

Edit Mode: Select Group BySelect Operation and Value ColumnResult

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:

Relative Date Filtering

For more information see link: Group By Function

Hope this help.

Regards

Abduvali

[ArzanP]

I don't want to group anything. Then I lose all my data. I want a new COLUMN, that counts how many times in the last 5 days a team has appears. And I want that to dynamically adjust based on the date in the row selected.

[ArzanP]

If it helps. Here is a link to my actual database.
https://1drv.ms/u/s!Aq4Lt9YzeAaU7FKV0g8S53W_6DhY

[vik0810 · Accepted 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))

[vik0810 · Accepted solution]

@ArzanP, where did you put your column at? I placed it in SBRData and it shows me correrct results, max value is 3 over the whole table. I checked the value for some teams and it looked correct.

Here is the copy of the file

[ArzanP]

Mate this is perfect. I'd hug you if I could. It seems i missed the very last part of the formula 🙂

[ArzanP]

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? 

 

[Abduvali]

Last 5 Days = calculate(COUNTROWS(SBRData), SBRData[Date].[day] - 5)

[Abduvali]

Very interesting piece of work =D 

Is this what you trying to achieve 

Let me know if it works for you.

Regards

Abduvali