How to calculate ongoing weekly totals per data type

[another1here]

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!

[Greg_Deckler · Accepted solution]

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

[another1here]

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

[another1here]

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

[another1here]

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

[Greg_Deckler]

Yeah, the Power BI folks tend to make fixes pretty quickly! Awesome, I'll have to try that.