cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DateAdd to get 12 last months of data

Hi there. 

 

I'm trying to create a simple statistical view of the amount of calls recieved the last 12 months.

 

To get the amount of calls in the current month I use

 

 

 

CountRows( Filter(DMETickets;Month(Oprettet)=Month(Today())))

 

 

 

To get the amount of calls i get in the last month i use

 

 

 

CountRows( Filter(DMETickets;Month(Oprettet)=Month(DateAdd(Today();-1;Months))))

 

 

 

With the current coding my current month and last month are giving the correct data.

 

But if i try to go one more month back and use the code 

 

 

 

CountRows( Filter(DMETickets;Month(Oprettet)=Month(DateAdd(Today();-2;Months))))

 

 

 

 

I get all of the remaining calls in the database. 

I am unsure if Month can't function when there is a change in year going on. 

 

Here's the data i am getting and what it should actually be

 

TheActual amount of callsWhat powerapps says
February (current month)55
January1515
December11133
November100

 

Am i using DateAdd(), Month() or Months() wrong in any way?

 

1 ACCEPTED SOLUTION

Accepted Solutions
gabibalaban
Super User
Super User

Hi @Anonymous ,

With this formula you will count all data having december as month, this means also the 2019 December etc. You need also to check for the year to get accurate results.

 

CountRows( Filter(DMETickets;Text(Oprettet;"yyyymm")=Text(DateAdd(Today();-2;Months);"yyyymm")))

 

I'm not quite sure if my formula don't throw a delegation warning .

 

Hope it helps ! 

View solution in original post

7 REPLIES 7
gabibalaban
Super User
Super User

Hi @Anonymous ,

With this formula you will count all data having december as month, this means also the 2019 December etc. You need also to check for the year to get accurate results.

 

CountRows( Filter(DMETickets;Text(Oprettet;"yyyymm")=Text(DateAdd(Today();-2;Months);"yyyymm")))

 

I'm not quite sure if my formula don't throw a delegation warning .

 

Hope it helps ! 

View solution in original post

Anonymous
Not applicable

Hi @gabibalaban.

 

I tried copying your code into december that reads 133 right (should be 11, not all entries from before january)

 

It still shows 133.

 

Thank you for trying

rubin_boer
Super User
Super User

hi @Anonymous 

I hope you dont mind a different approach.

Collect the ticket data and add a month column and total calls something like:

ClearCollect(_UpdatedCalls,AddColumns(_Calls,"Month",Text(Date(Value(Mid(Date,7,4)),Value(Mid(Date,4,2)),Value(Mid(Date,1,2))),"[$-en-US]mmm yyy"),"Total Calls",Sum(_Calls,Calls))) - (the total calls here will be for all the months, just a habit i have if i want to calc percentages for a specific month,i created dates as text)

Now group the data to get the sum per month

ClearCollect(_CallSummary,GroupBy(_UpdatedCalls,"Month","Monthly Summary"))

 

rubin_boer_0-1612771610162.png

hope it will help you.

 
Anonymous
Not applicable

@gabibalaban 

It would appear i am not thinking clearly.

 

I am using a SharePoint list and I havent been thinking clearly about the date field i am using for this.. 

 

Currently I have been trying to use "Oprettet" and should be using "Dato". 

 

Though i am still having a bit of trouble because that field is a normal text field and not a date field

 

@Anonymous ,

Is there a problem to keep your data (as text) in this format: "yyyy.mm.dd" ?  I like to use this to avoid delegation warnings.

You need to use also a context variables to keep this outside the filter formula, otherwise a delegation warning it will be raised:

 

UpdateContext({neededMonth:Text(DateAdd(Today();-2;Months);"yyyy.mm"))})

 

 And your formula will look like:

 

CountRows(Filter(DMETickets;StartsWith(Dato;neededMonth)))

 

I used StartsWith () because this can be delegated to Sharepoint, as Search() function not.

After, if you want that your query to be dynamic, you can change (-2 - hardcoded value) with another context variable, picked by the user from maybe a dropdown control. 

Anonymous
Not applicable

@gabibalaban 

I guess you can find a way to work with that. 

 

I have tried the following with different variations of days - months - years

 

 

UpdateContext({neededMonth0:Text(DateAdd(Today();0;Months);"[$-da-DK]dd-mm-yy");neededMonth1:Text(DateAdd(Today();-1;Months);"[$-da-DK]dd-mm-yy")})

 

 

So i tried creating more than 1 context - because I need 12 months to be viewed at the same time

Right now the updatecontext is just on a button for easy access

 

and then a label with the formula:

 

 

CountRows(Filter(DMETickets;StartsWith(Dato;neededMonth0)))

 

But it just returns 0 and not 5 as it should. 

 

@Anonymous ,

Well no ... My question was if your Dato column can be kept in "yyyy.mm.dd" format ? All my idea was based on this format. What I was interested in is the first 7 chars from that string ("yyyy.mm") to be compared with the context variable neededDate. 

 

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 (1,410)