Forum Discussion
How to calculate ongoing weekly totals per data type
Well, you will likely want to use WEEKNUM function to help group the weeks:
https://msdn.microsoft.com/en-us/library/ee634572.aspx
Then, you will likely want to create a measure like:
Applicants = COUNT([Candidate Name])
And then three measures like:
Applied = CALCULATE([Applicants],FILTER([Origin]="applied")) Sourced = CALCULATE([Applicants],FILTER([Origin]="sourced")) Referred = CALCULATE([Applicants],FILTER([Origin]="referred"))
Community ChampionWell, you will likely want to use WEEKNUM function to help group the weeks:
https://msdn.microsoft.com/en-us/library/ee634572.aspx
Then, you will likely want to create a measure like:
Applicants = COUNT([Candidate Name])
And then three measures like:
Applied = CALCULATE([Applicants],FILTER([Origin]="applied")) Sourced = CALCULATE([Applicants],FILTER([Origin]="sourced")) Referred = CALCULATE([Applicants],FILTER([Origin]="referred"))
Greg_Deckler That was super helpful. A follow-up question to this...
With my resulting weeknum numbers, if I am using this on a filtered set of data that includes 'previous 365 days', how do I change these weeknum numbers to:
[1] Appear in chronological order (week numbers from end of 2015 are greater numbers from the past weeks in April, but I still want to see these in chronological order)
[2] Display something other than a week number that is easily understood by a viewer of the report. Such as "week ending April 23, 2016" to represent week #17. (it doesn't really need to say week ending, just need a single date to be a more easily interpreted representation of which week 17 is.)
Thank you again!
(Also, for the benefit of others viewing this thread later, I used "COUNTA" as opposed to "COUNT" because the data it was counting was text data as opposed to numerical data)...
Applicants = COUNTA([Candidate Name])
- Greg_Deckler
another1here - Hmm, well on the first question, you would probably do something like a concatenate in a new column like:
WeekGroup = CONCATENATE(FORMAT([Date],"yyyy"),WEEKNUM([Date]))
Essentially you are going for something like:
201550
201551
201552
201553
20161
20162
You would have to group by these which will get your sorting right, but will likely make it even less intelligible to your users.
Perhaps something like this will get you there:
FriendlyWeekGroup = DATEVALUE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(MONTH([Date]),"/"),7*WEEKNUM([Date]/MONTH([Date]))),"/"),FORMAT([Date],"yyyy")))
Greg_Deckler You are super helpful! Thank you!
I ended up using a function I came across here, which essentially outlines the following:
The formula to return the Start date of the week is as follows:
=DATE(A2, 1, -2) - WEEKDAY(DATE(A2, 1, 3)) + B2 * 7
Where A2 is the year and B2 is the week number
Thank you again!
Greg_Deckler Wow, I just downloaded the April Desktop update, and this is now built-in!
In Query Editor, go to Add Column tab, select the column with the original date, and then in the ribbon, click the "Date" drop-down, go to Week, and select from any of the following [1] Week of Year, [2] Week of Month, [3] Start of Week, [4] End of Week.
I initially got very excited to see Week of Year... tried it, perfect! Then, I tried End of Week, and it was everything I ever wanted and more, in *almost* a single click :).
Funny how timing can be sometimes :)
Thanks again!