How to Calculate Duration of Matches

[Anonymous]

Hi Team,
I have a table as shown in below where column "Event" is my expected output. How do I calculted it as a column or Measure.

Event will be based on Year and Week and Non Blank Matches.
Exa- Year 2020 Week 1 - it shows 3 matches - 3 rows. But event will be unique as it happed on continuously for few week.

similarly for 2020 week 4,5,6 ,,,three matches all should be in event 2.

Event name should be unique.

[FreemanZ · Accepted solution]

hi @Anonymous 

It hides some complexity, one way of doing so is by adding a column with this:

EVENT2 = 
VAR _table1 =
CALCULATETABLE(
    TableName,
    FILTER(
        TableName,
        TableName[Matches]<>BLANK()
    )
)
VAR _table2 =
SUMMARIZE(
    _table1,
    TableName[Year],
    TableName[Week]
)
VAR _table3 =
ADDCOLUMNS(
    _table2,
    "YW",
    TableName[Year]&TableName[Week]
)
VAR _table4=
ADDCOLUMNS(
    _table3,
    "Index",
    RANKX(_table3, [YW], ,ASC)
)
VAR _year = [Year]
VAR _week = [Week]
VAR _value =
MINX(
    FILTER(
        _table4,
        _year = [Year]&&_week = [Week]
    ),
    [Index]
)
RETURN
IF(
    [Matches] = BLANK(),
    BLANK(),
    "Event "&_value
)

i tried and it worked like this:

p.s. Rather cumbersome i would say. So please do @ me, if some better solution is proposed.