web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Sort Gallery by One of...
Power Apps
Answered

Sort Gallery by One of Many Dates based on Priority of date and Exclusion fields

(0) ShareShare
ReportReport
Posted on by seraph787 149

I'm trying to do something that in PowerApps that I can do quite simply in Excel, but as I keep trying, it seems it gets more and more complex.

 

I have 6 different date fields that I need to use to sort my gallery. These dates are stored in Excel with the entire columns formated as a Date "*3/14/1999". The names of these fields are: 'BLS', 'ACLS', 'MDL', 'ProvExp', 'PrivExp', 'taskFU-Date'

 

  • I also need it to ignore some of these based off of whether the T/F value in 'tBLS', 'tMDL', and 'tPriv' field values are "True" or "False".
  • I need to have it compare these dates in a very nested format as well.
  • I created 3 labels where any True value returns the 'taskFU-Date' - the false values return as noted below:

In the first label,  if it's False, then it will find the newest date between 'BLS' and 'ACLS'

 

DateValue(If(ThisItem.tBLS="True","",If(ThisItem.ACLS>ThisItem.BLS,ThisItem.ACLS,ThisItem.BLS)))

In the second label, if it's false, then pass on the 'MDL' date.

 

DateValue(If(ThisItem.tMDL="True","",ThisItem.'MDL'))

3rd Label: If it's false, then find the oldest date between PrivExp and ProvExp.

 

Text(If(ThisItem.tPriv="True",ThisItem.'taskFU-Date',If(ThisItem.pExp>ThisItem.pProvExp,ThisItem.pProvExp,ThisItem.pExp)),ShortDate)

 

Now I'd like to create a 4th label that finds the oldest date from all of these but I can't get it to work

  • using If() statements (it says it expects # values)
  • I try with DateDiff() but it expects date values and I've tried forcing it to render as a date value via
    • Text(variable,ShortDate)
    • DateValue()
    • DateTimeValue()
    • Value()
    • Whatever I do, it's not a compatible format.

Am I overthinking it or am I not using the right tools for this?


My hope is to use the final date produced from this to sort my gallery.

 

---------------------------- Additional info requested by TheMexican

 

Okay, here is some stripped down data.

 

IDMDLBLSACLSPrivExpProvExptaskFU-DatetBLStPrivtMDLrBLSrPrivrMDLrFinalSortDate
646/30/201912/1/20185/1/20194/6/2018 5/1/2018FALSETRUEFALSE5/1/20195/1/20186/30/20195/1/2018
705/31/20187/1/2019 6/21/2018 5/1/2018FALSETRUEFALSE7/1/20195/1/20185/31/20185/1/2018
1712/31/20192/1/2016 6/21/2018 5/1/2018FALSETRUEFALSE2/1/20165/1/201812/31/20192/1/2016
719/30/20193/1/2018 10/11/2018 4/19/2018TRUEFALSEFALSE4/19/201810/11/20189/30/20194/19/2018
545/31/20209/1/2019 10/11/2018  FALSEFALSEFALSE9/1/201910/11/20185/31/202010/11/2018
4012/31/201911/1/2018 12/7/2018  FALSEFALSEFALSE11/1/201812/7/201812/31/201911/1/2018
149/30/20184/1/2019 1/19/20195/28/20184/28/2018FALSETRUEFALSE4/1/20194/28/20189/30/20184/28/2018
3211/30/20175/31/2019 11/28/2019  FALSEFALSEFALSE5/31/201911/28/201911/30/201711/30/2017
284/30/20195/1/2019 1/19/20197/24/20186/24/2018FALSETRUEFALSE5/1/20196/24/20184/30/20196/24/2018
574/30/202012/1/201812/1/20181/19/2019  FALSEFALSEFALSE12/1/20181/19/20194/30/202012/1/2018
594/30/202011/1/2019 3/16/2019  FALSEFALSEFALSE11/1/20193/16/20194/30/20203/16/2019
585/31/20189/1/2018 5/5/2019  FALSEFALSEFALSE9/1/20185/5/20195/31/20185/31/2018
747/31/20182/1/2018 5/5/2019 4/19/2018TRUEFALSEFALSE4/19/20185/5/20197/31/20184/19/2018
727/31/20187/1/2018 5/5/2019 7/1/2018TRUEFALSEFALSE7/1/20185/5/20197/31/20187/1/2018
393/31/201911/1/2019 9/27/2019  FALSEFALSEFALSE11/1/20199/27/20193/31/20193/31/2019
569/30/20198/1/2018 5/5/2019  FALSEFALSEFALSE8/1/20185/5/20199/30/20198/1/2018
755/31/20194/1/2018 5/9/2019 4/19/2018TRUEFALSEFALSE4/19/20185/9/20195/31/20194/19/2018

 

For the purposes of visualizing this data here in my post, I put the dates that match the last column in red.

 

The last FOUR columns are the formula columns I was using in excel (but for the purposes of PowerApps, I had to remove them because my app wouldn't read the table if it had formulas in any fields).  The formulas for the last 4 columns were:

  • 'rBLS'  =IF([@tBLS]=TRUE,[@[taskFU-Date]],MAX([@BLS],[@ACLS]))
  • 'rPriv' =IF([@tPriv]=TRUE,[@[taskFU-Date]],MIN([@PrivExp],[@ProvExp]))
  • 'MDL' =IF([@tMDL]=TRUE,[@[taskFU-Date]],[@MDL])
  • 'rFinalSortDate' =MIN([@rBLS],[@rPriv],[@rMDL])

I then sorted my table by 'rFinalSortDate'.

 

Final Result: In PowerApps, I'd like to sort my Gallery by that final sort date (but I have to find a way to make all the calculations that I was doing with Excel Formulas).

Categories:
Building Power Apps
I have the same question (0)
  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi seraph77, it would be very helpful for all of us to get a sample of how the data is stored in your Excel (It doesn't have to be real data) and what end result you want. Sometimes it is really hard to follow what you want with words.

    for example:

    Data source has 9At least 5 or 6 rows):

    ACLS  MDL   ProvExp          PrivExp              taskFU-Date

    09/23/2017   08/23/2017      07/12/2019        11/12/2018

    ...

    ....

     

    and then I want this result:

    ACLS

    09/23/2017

    07/12/2018

     

    This way we can all visualize your data and see what kind of result you need.

     

  • seraph787 Profile Picture
    seraph787 149 on at

    Hi TheMexican,

    I've updated the original post with the additional data.

  • seraph787 Profile Picture
    seraph787 149 on at
  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi Seraph787, here are the formulas I came up with:

    To calculate rBLS in your Gallery: 

    DateValue(If(ThisItem.tBLS="TRUE",ThisItem.'taskFU-Date',DateValue(ThisItem.BLS) > DateValue(ThisItem.ACLS),ThisItem.BLS,ThisItem.ACLS),"en-US")

    To calculate rMDL:

    DateValue(If(ThisItem.tMDL="FALSE",ThisItem.MDL,ThisItem.'taskFU-Date'),"en-US")

    To calculate rPriv

    DateValue(If(ThisItem.tPriv="TRUE",ThisItem.'taskFU-Date',DateValue(ThisItem.PrivExp)>DateValue(ThisItem.ProvExp),ThisItem.ProvExp,ThisItem.PrivExp),"en-US")

    I ran out of time at work, but I will check out your last label (4th) tomorrow.

  • seraph787 Profile Picture
    seraph787 149 on at

    Hi TheMexican,

     

    Thanks for this! 

     

    I am really eager to try this but there's an issue with my account that has essentially locked me out of accessing PowerApps for the past two days.  One of the Microsoft folks is helping me out with it.  Crossing my fingers it gets resolved soon so I can try these!

  • seraph787 Profile Picture
    seraph787 149 on at

    Okay I've been able to get back in and try those.

     

    I made a lot of headway with a few tweaks here and there.  When I went to create the 4th label, I got pretty close.. close enough to try sorting by the 4th label.  Except that's when I realized that no matter what I tried, I can't sort by a label. 

     

    So I did some more digging and found a Post by Jeff_Thorpe for a similar issue (I think getting this far prompted me to try some different variations of the forum searches I had done before). Although his specific answer wasn't marked as the accepted solution because of one minor fix needed below, it seems like it's exactly what I need.  So I set out to do the calculations right inside the Gallery Items field.

     

    What I decided was that I need to do four calculations there, the three that you helped me with earlier + plus one more to do the final calculations based on those three.

     

    SortByColumns(
 AddColumns(
 providerTable,
 "calcBLS",Text(If(tBLS="True",'taskFU-Date',If(BLS > ACLS,If(IsBlank(BLS),ACLS,BLS),If(IsBlank(ACLS),BLS,ACLS))),ShortDate),
 "calcMDL",Text(If(tMDL="False",'CA-MD-Lic-Exp','taskFU-Date'),ShortDate),
 "calcPriv",Text(If(tPriv="True",'taskFU-Date',If(pExp<pProvExp, If(IsBlank(pProvExp),pExp,pProvExp),If(IsBlank(pExp),pProvExp,pExp))),ShortDate),
 "calcMain",If(DateValue(Text("calcBLS",ShortDate),"en-US")>DateValue(Text("calcMDL",ShortDate),"en-US"),
	 If(DateValue(Text("calcMDL",ShortDate),"en-US")>DateValue(Text("calcPriv",ShortDate),"en-US"),DateValue(Text("calcPriv",ShortDate),"en-US"),DateValue(Text("calcMDL",ShortDate),"en-US")),
	 If(DateValue(Text("calcBLS",ShortDate),"en-US")>DateValue(Text("calcPriv",ShortDate),"en-US"),DateValue(Text("calcPriv",ShortDate),"en-US"),DateValue(Text("calcBLS",ShortDate),"en-US")))
),
 "calcMain",
 Ascending)

    But I am finding out something more... You can't perform the 4th calculation based on the 1st three.  In the code above, you'll see that for the 4th calculation "calcMain", I am referencing the previous columns in quotes.  I've tried this without quotes (calcBLS) with .Text (calcBLS.Text), without the Text(...,ShortDate), etc.  So the above "calcMain" was the only variant I could come up with to get it to stop the errors.  

     

    Once I got to 0 errors, I went ahead and created a label in my gallery to figure out what is actually being passed through.  I should have known... it was treating the "calcBLS" and other "calcMDL", etc.  As text strings, so the label would read: calcBLS.

     

    So am I right in that I can't calculate the 4th one inside the Gallery Items field?  Any other ways to calculate the 4th one and sort by it?

  • Verified answer
    Jeff_Thorpe Profile Picture
    Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

    Have you tried nesting the AddColumns. The inner AddColumns would create the first three column then the outter AddColumns would create the 4th column which uses the first three columns. Since the 4th column is created in the outter AddColumns the inner columns that were created should be available to use.

     

    SortByColumns(
 AddColumns(
 AddColumns(
 providerTable,
 "calcBLS",Text(If(tBLS="True",'taskFU-Date',If(BLS > ACLS,If(IsBlank(BLS),ACLS,BLS),If(IsBlank(ACLS),BLS,ACLS))),ShortDate),
 "calcMDL",Text(If(tMDL="False",'CA-MD-Lic-Exp','taskFU-Date'),ShortDate),
 "calcPriv",Text(If(tPriv="True",'taskFU-Date',If(pExp<pProvExp, If(IsBlank(pProvExp),pExp,pProvExp),If(IsBlank(pExp),pProvExp,pExp))),ShortDate)
 ),
	"calcMain",If(DateValue(Text(calcBLS,ShortDate),"en-US")>DateValue(Text(calcMDL,ShortDate),"en-US"),
	 If(DateValue(Text(calcMDL,ShortDate),"en-US")>DateValue(Text(calcPriv,ShortDate),"en-US"),DateValue(Text(calcPriv,ShortDate),"en-US"),DateValue(Text(calcMDL,ShortDate),"en-US")),
	 If(DateValue(Text(calcBLS,ShortDate),"en-US")>DateValue(Text(calcPriv,ShortDate),"en-US"),DateValue(Text(calcPriv,ShortDate),"en-US"),DateValue(Text(calcBLS,ShortDate),"en-US")))
 ),
 "calcMain",
 Ascending
)
  • seraph787 Profile Picture
    seraph787 149 on at

    It works!  This nesting solution with AddColumns is perfect for this.

     

    Thank you Jeff!

     

    And thank you TheMexican for helping me get the DateValues cleaned out for the first three calculations.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 329 Most Valuable Professional

#2
11manish Profile Picture

11manish 209 Super User 2026 Season 2

#3
Mohsin Ali Profile Picture

Mohsin Ali 179

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans