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 Championanother1here - 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!
- Greg_Deckler
Yeah, the Power BI folks tend to make fixes pretty quickly! Awesome, I'll have to try that.