Extract months from Date column in Collection

[lovishsood1]

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: 

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.

[WarrenBelz]

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]

Facing issue in GroupBy Statement!

[WarrenBelz]

@lovishsood1 ,

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

[lovishsood1]

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 :

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

[WarrenBelz · Accepted solution]

@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

[lovishsood1]

Thanks a lot, Warren.

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

[WarrenBelz]

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

[lovishsood1]

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

But Yeah, it worked.