Group data by month and year with fetchXML not working in Power Automate

(1) Share

Report

Posted on by leo85

635

Hi,

I'm trying get the information how often each users logged into a specific environment each month. To do this I created a flow, which get the access information from the audit history and combines it with the user data. I'm doing all of this with a fetchXml statement.

I want to group the data by user, year and month, but the aggregate for the date is not working. In the "Day" field it simply shows the timestamp and the aggregate count is always 1.

Here is the fetchxml:

<fetch aggregate='true' mapping='logical' version='1.0' distinct='false'>
<entity name='audit'>
<attribute name='createdon' alias='Year' groupby='true' dategrouping='year' />
<attribute name='createdon' alias='Day' groupby='true' dategrouping='day' />
<attribute name='auditid' alias='Total' aggregate='count' />
<filter type='and'>
<condition attribute='operation' operator='eq' value='4' />
<condition attribute='createdon' operator='ge' value='2024-01-01T00:00:00.000Z' />
</filter>
<link-entity name='systemuser' from='systemuserid' to='objectid' link-type='inner' alias='user'>
<attribute name='domainname' alias='UPN' groupby='true' />
</link-entity>
</entity>
</fetch>

What may be the issue here?

Categories:

Microsoft Dataverse with Power Apps

All responses (3)

Answers (0)

Sort by

MichaelFP 2,008 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Hello. I tested here it is look like an issue with date and time field on Audit table, it looks that is not threating as Date and time the field created on. it only showing the full date and time when retrieve the data.

I tested with account with the created on and it worked.

I would recommend to open a ticket with Microsoft to check that.
Suggested answer

Shashank Bhide 946 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

have you tried this

https://admin.powerplatform.microsoft.com/analytics/d365ce
Ravindra Jadhav 342 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Please Closed the Question, Mark it Solved

If my answer helped resolve your issue, please consider marking it as solved to assist others facing the same problem. Additionally, giving it a like would be greatly appreciated and motivates us to keep helping

Thank You

Ravindra Jadhav

Please Subscribe to https://www.youtube.com/@jadhav_ravi_oo7

Connect On https://www.linkedin.com/in/ravindra-jadhav-powerplatform/