Structuring data that doesn't use dates

[Hickling]

Hi team,

Doing a little side project for fun for fantasy football and I've got a question. All of my professional use of Power BI has been based on monthly reporting, which makes things like weighted moving averages and the like very easy as you're using the date functions of DAX.

However, the player performance stats I have for football uses rounds, not dates, so the question becomes how do I perform date like functions when all I have is a column with season and a column with round. Don't have a specific function in mind, just curious how I would write DAX that uses the most recent results or weights most recent results as they're updated each round. Currently have stats from 2017-2019 and will be adding 2020 stats as games are played. Data looks roughly something like:

Player - Season - Round - Data

A        -  2020    -     1     -   xx

B        -  2020    -     1     -   xx

C        -  2020    -     1     -   xx

A        -  2020    -     2     -   xx

B        -  2020    -     2    -   xx

C        -  2020    -     2    -   xx

A        -  2020    -     3    -   xx

B        -  2020    -     3    -   xx

C        -  2020    -     3    -   xx

[amitchandak]

Create date like

//New Column

date = date(table[season],1,1). Create a Calendar time intelligence function and use that for time related comparision.

Also, create dimension for round and player.

// New table

player dim = distinct(table[player])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Hickling]

Hi @amitchandak I did consider using the season and rounds to create pseudo dates to work with, the problem is finding a consistency in periods. Say if I wanted to do a weighted average of the last x games which would look something like (Score * weighting) + (Score previous period * weighting-1) + (Score previous period * weighting-2) and so on. Previously I have done this just using edate month-1 type stuff, this works within a season since I could just use day increments but it doesn't help across seasons since I'd be jumping a year.

I have come up with a solution using 2 calculated columns - the first is a simple concatenation of season and round so it's like 201701, 201702 .... 202001. Then a Rankx column so that as new seasons and rounds get added the most recent will always be rank 1. Then I can do calculations using the rank column as the filter, which I think is the neatest and easiest?

Concatenate = if('2016-2019'[Round]<10,CONCATENATE('2016-2019'[Season]&"0",'2016-2019'[Round]),CONCATENATE('2016-2019'[Season],'2016-2019'[Round]))

Rank = RANKX('2016-2019','2016-2019'[Concatenate],,DESC,Dense)

Interested to hear if someone has a better suggestion.