Forum Discussion
IF Statement in PowerBI
Hi,
Basically want to create an IF statement on a table in PowerBI which calculates the following
IF either Date 1 OR Date 2 columns have a date within the next 3 months, it will return the data.
Example original table:
| ID | Date 1 | Date 2 | Note |
| 001 | 01/11/2023 | 05/11/2023 | Both dates are within next 3 month |
| 002 | 12/12/2023 | 02/04/2024 | Date 1 is within next 3 months |
| 003 | 05/12/2023 | 05/12/2023 | Both dates are within next 3 months |
| 004 | 07/04/2024 | 14/12/2023 | Date 2 us within next 3 months |
| 005 | 20/02/2024 | 23/02/2024 | Neither date is within next 3 months |
Resulting data:
ID005 has been excluded
| ID | Date 1 | Date 2 |
| 001 | 01/11/2023 | 05/11/2023 |
| 002 | 12/12/2023 | 02/04/2024 |
| 003 | 05/12/2023 | 05/12/2023 |
| 004 | 07/04/2024 | 14/12/2023 |
Tried to explain as well as I can. Any help greatly appreciated!
Thanks!
2 Replies
- Alef_Ricardo_
Resolver II
Hello! You can achieve this in PowerBI by creating a new calculated column that checks if either `Date 1` or `Date 2` is within the next 3 months. Then, you can filter your table based on this new column. Here's an example of how you can do it:
```DAX
WithinNext3Months =
VAR CurrentDate = TODAY()
VAR ThreeMonthsLater = EDATE(CurrentDate, 3)
RETURN
IF(
(Table[Date 1] >= CurrentDate && Table[Date 1] <= ThreeMonthsLater) ||
(Table[Date 2] >= CurrentDate && Table[Date 2] <= ThreeMonthsLater),
"Yes",
"No"
)
```In this code:
- `CurrentDate` is today's date.
- `ThreeMonthsLater` is the date three months from today.
- The `IF` statement checks if either `Date 1` or `Date 2` is between `CurrentDate` and `ThreeMonthsLater`. If yes, it returns "Yes". Otherwise, it returns "No".After creating this calculated column, you can filter your table to only show rows where `WithinNext3Months` is "Yes".
- parry2k
Super User
FizzyMoon67 You can add a calculated column and then filter for that column where Flag = 1
Flag = VAR __Current = TODAY () VAR __DaysToCheck = 90 VAR __Date1Diff = DATEDIFF ( __Current, MyDates[Date 1], DAY ) VAR __Date2Diff = DATEDIFF ( __Current, MyDates[Date 2], DAY ) RETURN SWITCH ( TRUE (), __Date1Diff <= __DaysToCheck || __Date2Diff <= __DaysToCheck, 1, 0 )You can tweak it if you need a precise 3 months, I just checked 90 days.
