Unanswered

Filter on year and month, year greater than or equal to this year and month greater than current month

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I have a gallery that is getting the sum of a column from another table as a value. This needs to filter on just the rows where date is greater than Today's date.

From my understanding and testing PowerApps won't allow my to do a straight
Sum(
Filter( 'Table2',
Label2 = ProjectID,

Date > Today()

),
Orderssum
)

Instead I have two columns that I think could be useful to filter on. Year and month (in digits).
Is it possible and if so, how to inside my filter function add a condition if year >= Year(Today()) and if that's true then month > Month(Today()).

Not sure how to work around this.

Thank you very much!

Categories:

Building Power Apps

All responses (5)

Answers (0)

Sort by

CNT 10,921 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous Is the Date column in 'Table2' of type date? Could you also share a screen shot.
JR-BejeweledOne 5,836 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Is the date you want to filter on an actual date column or is it a text field? I am able to successfully filter a gallery like this. If the date in your table is not an actual date column you might have to wrap it like this: DateValue(Datecolumn) to make it work.

Filter(PowerAppsData, DateTest > Today())

And am able to successfully sum a column the same way you are trying.

Sum( Filter(PowerAppsData, DateTest < Today()), Number)
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@CNT, yes, Date is of type date, while year and month dolumn in 'Table2' is of type INT.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, I'm not allowed to do that, my gallery does not filter on date, just my one label, which is referencing another table ('Table2'). Powerapps tells me that it cannot apply operator < to DateTimeZone and Date.
JR-BejeweledOne 5,836 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
It appears also that you have an error in your formula. In order to filter on 2 conditions, you need to use the And function.

This:

Sum(
Filter( 'Table2',
Label2 = ProjectID,

Date > Today()

),
Orderssum
)

Should be:

Sum( Filter( 'Table2', Label2 = ProjectID && Date > Today() ), Orderssum )