cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sam_sonepar
Helper III
Helper III

Sum Distinct Dates

Hello

 

I have a Dataverse table with these relevant columns

- Appointment Date

- Forecasted Skids

 

The items are displayed in a Gallery using this formula for Items

Sort(
    Distinct(
        Filter(
            'Inbound Planner Main Tables',
            'Appointment Date' >= dteFromForecastSummary_AS.SelectedDate && 'Appointment Date' <= dteToForecastSummary_AS.SelectedDate
        ),
        Text(
            'Appointment Date',
            "[$-en-US]mm/dd/yyyy"
        )
    ),
    Result,
    Ascending
)

 

The goal here is to display the various dates between Date Picker 1 (dteFromForecastSummary_AS) and Date Picker 2 (dteToForecastSummary_AS) without repeating them, therefore the reason why I added the distinct function.

 

This is how it's not supposed to look like

Appointment DateForecasted Skids
July 610
July 63
July 62
July 71
July 72

 

This is how it is supposed to look like

Appointment DateForecasted Skids
July 615
July 73

 

The date part is working fine.

The issue is with the sum of the other value.

 

This gives me 0 so I'm not sure what I can do.

Sum(Filter('Inbound Planner Main Tables','Appointment Date'=ThisItem.Result),'Forecasted Skids')

 

Any help is appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @sam_sonepar ,

This will get you a table with two columns - Appointment and Forecast with the data you want

AddColumns(
   GroupBy(
      AddColumns(
         Filter(
            'Inbound Planner Main Tables',
            'Appointment Date' >= dteFromForecastSummary_AS.SelectedDate && 
            'Appointment Date' <= dteToForecastSummary_AS.SelectedDate
         ),
         "Appointment"
         Text(
            'Appointment Date',
            "[$-en-US]mm/dd/yyyy"
         )
      ),
      "Appointment",
      "Data"
   ),
   "Forecast",
   Sum(Data,'Forecasted Skids')
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

1 REPLY 1
WarrenBelz
Super User
Super User

Hi @sam_sonepar ,

This will get you a table with two columns - Appointment and Forecast with the data you want

AddColumns(
   GroupBy(
      AddColumns(
         Filter(
            'Inbound Planner Main Tables',
            'Appointment Date' >= dteFromForecastSummary_AS.SelectedDate && 
            'Appointment Date' <= dteToForecastSummary_AS.SelectedDate
         ),
         "Appointment"
         Text(
            'Appointment Date',
            "[$-en-US]mm/dd/yyyy"
         )
      ),
      "Appointment",
      "Data"
   ),
   "Forecast",
   Sum(Data,'Forecasted Skids')
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,278)