Forum Discussion
How to calculate ongoing weekly totals per data type
Hi - I have raw data (see image below) with dates of applicants and how they came to enter our database ("Origin").
What I am attempting to do, is to break this into weekly data sets to see totals by week (and later, by month, and then quarter).
But first I need to start with weekly data. An ideal end result might look like the below image, although I would probably also use a visualization to show week by week trends as well.
This may be very easy to do via PowerBI Desktop, but I'm stuck (newbie alert). How do I break the data into weeks and display each week with the total of each of the Origin types for that week? (By the way, I don't mind whether it's a "week of" or a "week ending" as long as it's a 7 day period that each week consists of).
Thanks in advance for any assistance you can give me with this!
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"))
6 Replies
- Greg_Deckler
Community Champion
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"))
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")))
