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
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (2,512)