Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
firdaus_akmal28
New Member

How to do grouping in specific date

Hi all, i have an issue regarding "specific date" here..its begin when i have to arrange / to group the specific date to be generated in my reports. hope everyone's here will understand and teach me/suggest me/enlighten me on how to solve my issue with the best result in power BI.

million thanks to all of u in advance. rgds.

 

Scenario

IF date => 01 jan 2016 & <= 05 jan 2016, show flag "Festival A"

IF date => 25 feb 2016 & <= 28 feb 2016, show flag "Festival B"

IF date => 06 mar 2016 & <= 07 mar 2016, show flag "Festival C"

IF date => 16 apr 2016 & <= 02 may 2016, show flag "Festival D"

 

DateFestival
1 jan 2016 - 5 jan 2016festival A
25 feb 2016 - 28 feb 2016festival B
6 mar 2016 - 7 mar 2016festival C
16 apr 2016 - 2 may 2016festival D
1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @firdaus_akmal28,

 

In this scenario, you can consider two solutions to display festival type in a new column.

 

Solution 1.
You can add a calculated column directly using this formula:

Festival =
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 complex. Then, you can try below solution.

 

Solution 2.

Suppose there are several columns in your source table, now you need to create a new table which only includes one date column.

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

 

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

6.PNG 

 

Cross join above two tables. And add 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, use LOOKUPVALUE function to add a new column.

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

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yulgu-msft
Employee
Employee

Hi @firdaus_akmal28,

 

In this scenario, you can consider two solutions to display festival type in a new column.

 

Solution 1.
You can add a calculated column directly using this formula:

Festival =
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 complex. Then, you can try below solution.

 

Solution 2.

Suppose there are several columns in your source table, now you need to create a new table which only includes one date column.

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

 

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

6.PNG 

 

Cross join above two tables. And add 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, use LOOKUPVALUE function to add a new column.

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

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

to @v-yulgu-msft,

 

MAY GOD BLESS U always Yuliana!!!u did helped me a lot...i wish that i can give u a hug sis....btw thank u so **bleep** much for helping me.... Smiley Very Happy

v-yulgu-msft
Employee
Employee

Hi @firdaus_akmal28

 

In this scenario, you can consider two solutions to display festival type in a new column.

 

Solution 1
You can add a calculated column directly using this formula:

 

Festival =
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 complex. Then, you can try below solution.

 

Solution 2

Suppose there are several columns in your source table, now you need to create a new table which only includes one date column.

 

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

 2.PNG

 

Create an extra table to list the mapping relationship between date range and festival type.

 

 1.PNG

 

Cross join above two tables. And add a calculated column.

 

Table2 = CROSSJOIN(Table1,FestivalTable2)

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

 3.PNG

 

Create another calculate table to filter records from Table2.

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

4.PNG

 

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

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

5.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yulgu-msft
Employee
Employee

Hi @firdaus_akmal28,

 

In this scenario, you can consider two solutions to display festival type in a new column.

 

Solution 1.
You can add a calculated column directly using this formula:

Festival =
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 complex. Then, you can try below solution.

 

Solution 2.

Suppose there are several columns in your source table, now you need to create a new table which only includes one date column.

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

2.PNG

 

 

Create an extra table to list the mapping relationship between date range and festival type.
1.PNG

 

Cross join above two tables. And add a calculated column.

Table2 = CROSSJOIN(Table1,FestivalTable2)

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

3.PNG

 

Create another calculate table to filter records from Table2.

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

4.PNG

 

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

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

5.PNG

 

Best regards,
Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yulgu-msft
Employee
Employee

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.
1.PNG

 

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])

2.PNG

 

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 ()
)

3.PNG

 

Create another calculate table to filter records from Table2.

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

4.PNG

 

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

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

5.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.