cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter with IF statement

Hi Guys,

 

As you below can see I have 2 different galleries. Gallery 1 must show the data of next month, and gallery 2 must show the month after next month in this case January 2020. 

 

The first gallery show the exact data. This the formula that I use. 

Spoiler
Filter('[dbo].[Kopie van operationeel]', Year(Period) = Year(Today()) && Month(Period) = Month(DateAdd(Today(), 1, Months)))

You feel the question coming how do I have to do for Gallery 2. because when I use  2, Months)) in stead  of1 then I see January 2019 in stead of January 2020.

 

I can put 1 + year like I did for month but this is not the solution because it is not dynamic.

 

Period is date column in my SQL table.

 

Thanks in advance!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
chrisog
Responsive Resident
Responsive Resident

Hi @Anonymous,

 

If I understand this correctly, the issue is the statement 'Year(Period) = Year(Today())', not the months. The reason is that the first half of your conditional and statement is searching for This Year, when it should be matching the year of the date 1 (or 2) months from today. Does this make sense?

 

 

Readjust your formula for Gallery1 to read:

Filter('[dbo].[Kopie van operationeel]', Year(Period) = Year(DateAdd(Today(), 1, Months)) && Month(Period) = Month(DateAdd(Today(), 1, Months)))

 

And the Gallery2 formula to read:

Filter('[dbo].[Kopie van operationeel]', Year(Period) = Year(DateAdd(Today(), 2, Months)) && Month(Period) = Month(DateAdd(Today(), 2, Months)))

View solution in original post

2 REPLIES 2
chrisog
Responsive Resident
Responsive Resident

Hi @Anonymous,

 

If I understand this correctly, the issue is the statement 'Year(Period) = Year(Today())', not the months. The reason is that the first half of your conditional and statement is searching for This Year, when it should be matching the year of the date 1 (or 2) months from today. Does this make sense?

 

 

Readjust your formula for Gallery1 to read:

Filter('[dbo].[Kopie van operationeel]', Year(Period) = Year(DateAdd(Today(), 1, Months)) && Month(Period) = Month(DateAdd(Today(), 1, Months)))

 

And the Gallery2 formula to read:

Filter('[dbo].[Kopie van operationeel]', Year(Period) = Year(DateAdd(Today(), 2, Months)) && Month(Period) = Month(DateAdd(Today(), 2, Months)))

Anonymous
Not applicable

Hi @chrisog 

 

Perfect thanks!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,234)