Forum Discussion

another1here's avatar
another1here
Frequent Visitor
10 years ago
Solved

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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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's avatar
another1here
Frequent Visitor
10 years ago

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

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