DateAdd to get 12 last months of data

[Anonymous]

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

The	Actual amount of calls	What powerapps says
February (current month)	5	5
January	15	15
December	11	133
November	10	0

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

[gabibalaban · Accepted solution]

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 ! 

[Anonymous]

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]

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"))

hope it will help you.

[Anonymous]

@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

[gabibalaban]

@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]

@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. 

[gabibalaban]

@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.