Forum Discussion

rtiffe's avatar
rtiffe
Frequent Visitor
2 years ago

Creating a new measure

Hi, I am trying to find the max sum of TOA within the first, second and third quarter of the last 4 weeks of each month per quarter.  For Quarter four I just need the max sum of TOA for the quarter...
Need Help
Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
The Weekending_Date__c is a date data type

looks more like a text type to me.

 

the last 4 weeks of each month per quarter.

Please be more specific. Does this include partial weeks? What is your week numbering methodology?  Weeks and months are not compatible.  When do your quarters start?

 

Ideally all of these questions should be answered by an external calendar table that clearly marks the weeks in the month, the weeks in the quarter, and then weeks in the year etc.

  • rtiffe's avatar
    rtiffe
    Frequent Visitor
    2 years ago

    I edited the Weekending Date to a short date so in the power query the column data is in short date format (3-15-2022) so it is not a text type.

    q1 = jan - march

    q2 = april-june

    q3 = july-sept

    q4=oct- dec

     

    So for Q1 the number I need to calculate would be the max TOA number of the last 4 weeks in Q1. So by looking at the excel file the answer would be 3486.