Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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"
Date | Festival |
1 jan 2016 - 5 jan 2016 | festival A |
25 feb 2016 - 28 feb 2016 | festival B |
6 mar 2016 - 7 mar 2016 | festival C |
16 apr 2016 - 2 may 2016 | festival D |
Solved! Go to Solution.
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.
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
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.
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
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....
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 to list the mapping relationship between date range and festival type.
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 ()
)
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
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 to list the mapping relationship between date range and festival type.
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 ()
)
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
97 | |
93 | |
87 | |
68 |
User | Count |
---|---|
173 | |
134 | |
132 | |
102 | |
95 |