cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lovishsood1
Helper II
Helper II

Extract months from Date column in Collection

Hello,

 

I have created a collection where I'm grouping data on the basis of Id and Two Date Columns(From Date & ToDate).

 

Code : 1st Collection

ClearCollect(LeaveHistoryDates,AddColumns(LeaveHistoryCollectionDummy,"FromDate",Text(DateValue(tm_fromdate),DateTimeFormat.ShortDate),"ToDate",Text(DateValue(tm_todate),DateTimeFormat.ShortDate)));


Collection 2 where I'm grouping data : 

ClearCollect(LeaveHistoryDUmmy,GroupBy(LeaveHistoryDates,"tm_employeeid","FromDate","ToDate","Total Days"));



Collection 3 : 

ClearCollect(GroupEmpIdLeaves,AddColumns(LeaveHistoryDUmmy,"Total Leaves",Sum('Total Days',tm_numofdays)));

In the above collection, Sum of Number of Leaves w.r.t Id and Dates Columns.

The output of the Above Collection: 

lovishsood1_0-1658300547976.png

 


Only a Single date is showing not the whole month's dates. I need to show Month Names, not Dates.

My Problem: I want to show the leaves of employees on a Monthly Basis. 

 

 X-Axis: Month Names

Y-Axis: Total Leaves of particular Employee Id

 

 

I hope you understood my problem.

 

In short, need to show leaves for all 12 months on Chart but both Date Columns contain Dates in dd/mm/yyyy format.

 

Also, Tried Text Function. Still not able to figure it out.

1 ACCEPTED SOLUTION

Accepted Solutions

@lovishsood1 ,

Below is my best effort without building a model 

With(
   {
      wMonths: 
      ForAll(
         Sequence(
            DateDiff(
               YourStartDateHere,
               YourEndDateHere,
               Months
            ) + 1
         ),
         Text(
            DateAdd(
               YourStartDateHere,
               Value - 1,
               Months
            ),
            "mmmm, yyyy"
         )
      ),
      wDays: 
      AddColumns(
         GroupBy(
            AddColumns(
               LeaveHistoryCollectionDummy,
               "FromDate",
               Text(
                  DateValue(tm_fromdate),
                  "mmmm, yyyy"
               )
            ),
            "tm_employeeid",
            "FromDate",
            "Total Days"
         ),
         "Total Leaves",
         Sum(
            'Total Days',
            tm_numofdays
         )
      )
   },
   AddColumns(
      wMonths,
      "Employee",
      LookUp(
         wDays,
         Value = FromDate
      ).tm_employeeid,
      "TotalDays",
      LookUp(
         wDays,
         Value = FromDate
      ).'Total Leaves'
   )
)

 

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.

Visit my blog Practical Power Apps

View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

Hi @lovishsood1 ,

The fundamental problem here is that leave could span two or more months and to work all that out would be highly complex (an understatement). If for instance you wanted all the leave starting in each month

AddColumns(
   GroupBy(
      AddColumns(
         LeaveHistoryCollectionDummy,
         "FromDate",
         Text(DateValue(tm_fromdate),"mmm-yyyy")
      ),
      "tm_employeeid",
      "FromDate",
      "Total Days"
   ),
   "Total Leaves",
   Sum(
      'Total Days',
      tm_numofdays
   )
)

 

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.

Visit my blog Practical Power Apps

lovishsood1_1-1658310000380.png

Facing issue in GroupBy Statement!

@lovishsood1 ,

Just an extra comma (now fixed - I thought you might have spotted that)

Yeah, I did after replying. 🙂 
How do I show months which are not there in Collection.

 

I have taken another collection of MonthsName in order to sort the Months. But not able to show blank months.

Collection for Sorting Months:

 

ClearCollect(MonthNameSort,{Month:"January",value:1},{Month:"February",value:2},{Month:"March",value:3},{Month:"April",value:4},{Month:"May",value:5},{Month:"June",value:6},{Month:"July",value:7},{Month:"August",value:8},{Month:"September",value:9},{Month:"October",value:10},{Month:"November",value:11},{Month:"December",value:12})

 


on Column Chart's Items Property :

 

Sort(AddColumns(Filter(GroupEmpIdLeaves,tm_employeeid=CurrentUserEmp.EmployeeId),"MonthOrder2",LookUp(MonthNameSort,Month=MonthName).value),MonthOrder2,Ascending)

 

 
Output :

lovishsood1_0-1658387470421.png

My Problem: How do I show Blank months? (January,....., November, Dec...)

@lovishsood1 ,

Below is my best effort without building a model 

With(
   {
      wMonths: 
      ForAll(
         Sequence(
            DateDiff(
               YourStartDateHere,
               YourEndDateHere,
               Months
            ) + 1
         ),
         Text(
            DateAdd(
               YourStartDateHere,
               Value - 1,
               Months
            ),
            "mmmm, yyyy"
         )
      ),
      wDays: 
      AddColumns(
         GroupBy(
            AddColumns(
               LeaveHistoryCollectionDummy,
               "FromDate",
               Text(
                  DateValue(tm_fromdate),
                  "mmmm, yyyy"
               )
            ),
            "tm_employeeid",
            "FromDate",
            "Total Days"
         ),
         "Total Leaves",
         Sum(
            'Total Days',
            tm_numofdays
         )
      )
   },
   AddColumns(
      wMonths,
      "Employee",
      LookUp(
         wDays,
         Value = FromDate
      ).tm_employeeid,
      "TotalDays",
      LookUp(
         wDays,
         Value = FromDate
      ).'Total Leaves'
   )
)

 

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.

Visit my blog Practical Power Apps

Thanks a lot, Warren.

 

It is great for me but the drawback is I cannot show Blank months here.

Hi @lovishsood1 ,

The whole idea of that code is to show blank months - the first table is all the months between the dates you specify, the second is the Grouped code and the bottom adds the second to the first.

 

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.

Visit my blog Practical Power Apps

Yeah, Thanks a lot. Don't know why It did not give me blank months in the first attempt. 

But Yeah, it worked.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,854)