Filter with IF statement

[Anonymous]

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!

[chrisog · Accepted solution]

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]

Hi @chrisog 

Perfect thanks!