How to do grouping in specific date

Hi firdaus_akmal28,

In this scenario, you can consider two solutions to show Festival type in a calculated column.

Solution 1.

Add calculated column directly via this formula:

Column =
IF (
    FestivalTable[Date] >= DATE ( 2016, 1, 1 )
        && FestivalTable[Date] <= DATE ( 2016, 1, 5 ),
    "Festival A",
    IF (
        FestivalTable[Date] >= DATE ( 2016, 2, 25 )
            && FestivalTable[Date] <= DATE ( 2016, 2, 28 ),
        "Festival B",
        IF (
            FestivalTable[Date] >= DATE ( 2016, 3, 6 )
                && FestivalTable[Date] <= DATE ( 2016, 3, 7 ),
            "Festival C",
            IF (
                FestivalTable[Date] >= DATE ( 2016, 4, 16 )
                    && FestivalTable[Date] <= DATE ( 2016, 5, 2 ),
                "Festival D",
                BLANK ()
            )
        )
    )
)

But if you have many festival types, the above expression will be very complex. Then, you can try below solution.

Solution 2.

Create an extra table (in my test, it's Table1) to list the mapping relationchip between date range and festival type.

Suppose your source table contains several columns, now you need to create a calculate table that only includes the specific date column.

FestivalTable2 = SELECTCOLUMNS(FestivalTable,"Date",FestivalTable[Date])

Then, cross join the above two tables. And create a calculated column (in my test, it's Flag).

Table2 = CROSSJOIN(Table1,FestivalTable2)

Flag =
IF (
    Table2[Date] >= Table2[StartDate]
        && Table2[Date] <= Table2[EndDate],
    Table2[Festival],
    BLANK ()
)

Create another calculate table to filter records from Table2.

Table3 = CALCULATETABLE(Table2,Table2[Flag]<>BLANK())

At last, in your original table, us LOOKUPVALUE function to add a new column.

Festival = LOOKUPVALUE(Table3[Flag],Table3[Date],FestivalTable[Date])

Best regards,
Yuliana Gu