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