DAX - categorise event using dates from another table

Question

I have a table of games, each of which has an occurrence date, such as...

-------------------
Game    |   Date   |
-------------------
Game A  | 01/06/16 |
-------------------
Game B  | 01/06/14 |
-------------------
Game C  | 01/06/18 |
-------------------

Then I have a second table defining periods of time with a start/end date - basically, seasons...

--------------------------------
Season  |   Start   |    End   |
--------------------------------
2014/15 |  15/05/14 | 25/08/15 |
--------------------------------
2015/16 |  12/09/15 | 22/08/16 |
--------------------------------
2016/17 |  10/09/16 | 25/08/17 |
--------------------------------

What I'd like to do, is add a column to the 'Events' table which uses the Seasons table to calculate the Season in which a game happened so that I can filter games by season. So the end result would be...

-------------------------------
Game    |   Date   |  Season  |
-------------------------------
Game A  | 01/06/16 |  2015/16 |
-------------------------------
Game B  | 01/06/14 |  2014/15 |
-------------------------------
Game C  | 01/06/17 |  2016/17 |
-------------------------------

I've managed to do it a long-winded way using a custom column and nested IF statements, but that seems messy and requires modification to the IF statement when a new season starts, which would be a pain.

I'm a bit stumped as to where I start with this, I could knock it out in seconds in SQL but DAX is fairly new to me.

How do you want to handle games which overlap more than one season? eg Game A is in both 2015/16 and 2016/17. — Olly
Sorry, that's my poor example - there should be no overlap between the season dates, games can only occur in one season. I've updated my demo table to correct the mistake. — Hill79

Olly Olly · Accepted Answer · 2019-04-12T11:20:00

This will return the earliest (or only) matching season:

Season First = 
CALCULATE ( 
    FIRSTNONBLANK ( Seasons[Season], 1 ),
    FILTER ( Seasons, Games[Date] >= Seasons[Start]  && Games[Date] <= Seasons[End] )
)

If you DID have overlapping seasons, this calculated column returns them in a list:

Seasons = 
CONCATENATEX ( 
    FILTER ( Seasons, Games[Date] >= Seasons[Start]  && Games[Date] <= Seasons[End] ),
    Seasons[Season], 
    ","
)

Worked example PBIX file: https://pwrbi.com/so_55649635/

DAX - categorise event using dates from another table

1 Answers