Answered

Dynamic SortByColumns

(0) Share

Report

Posted on by rmc

I have a table of sales by month number and want to display that as trailing 12 months eg: If we are in April (month number 4) the display sales staring month 4 (April) thru month 3 (March).

I put all the format strings in another table SortText by month and dynamically lookup that string using

LookUp(SortText,MonthNumber=Month(Now()),SortOrderMonths) which returns [ 4,5,6,7,8,9,10,11,12,1,2,3 ]

this statement does not work and no error given

SortByColumns(SalesTable, "MonthNumber", LookUp(SortText,MonthNumber=Month(Now()),SortOrderMonths))

If you hardcode it like this
SortByColumns(SalesTable, "MonthNumber",[ 4,5,6,7,8,9,10,11,12,1,2,3 ])

then it works perfectly!

If anyone has any ideas, I can't see why it is not working.

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

PowerAddict 7,316 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
The problem is when you are doing a LookUp using the table that has your sortorders, it returns a text and that's why it doesn't work.

You might want to try something like this:

SortByColumns(SalesTable, "MonthNumber", Switch(Month,4, [ 4,5,6,7,8,9,10,11,12,1,2,3 ], 5, [5,6,7,8,9,10,11,12,1,2,3,4))

Let me know if this helps.

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions.

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube | Email

Was this reply helpful? Yes No
rmc 51 on at

Like
a
(1)

Report
Copy link

Link copied!

Thanks Hardit!

Worked perfectly. Would never of thought to use Switch statement here.

Mark

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Glad I could help! Good luck with the rest of the app! Feel free to reach out in case of any questions!

Thanks!

Hardit Bhatia

Blog | Twitter | LinkedIn | Facebook | YouTube | Email

Was this reply helpful? Yes No