Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Matrix by quarters

 

 

 

 

Hey guys,

 

I have the following data table:

StreamStart DateSalary
DevJan 1, 2022100,000
SalesMar 3, 2022120,000
SalesJuly 1, 2022100,000
PMOct 10, 2022140,000

 

I am trying to display a matrix like this, where I can estimate the cost of each resource by quarter based on their salary (i.e Salary/12 x 3 for each quarter depending on the month in the quarter they sarted):

StreamQuarter 1Quarter 2Quarter3Quarter 4
Dev    
Sales    
PM    

 

The Start Date represent the starting date of the resource with their annual salary.

I need a function to determine the quarterly salary of that resource broken down into 4 quarter, starting from quarter his start date falls into.

Then using that function in a Matrix visual display the above table.

 

For example, if a resource started in June 2022 with a salary of 100K The table would display

Stream Q1  Q2    Q3    Q4

Dev      0$   25K  25K  25K

 

if he started in july then Q2 would reflect that month.

 

Your help is appreicated.

 

Thank you

  • Icey's avatar
    Icey
    4 years ago

    Hi Anonymous ,

     

    How about this:

    Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "QuarterNum", QUARTER ( [Date] ),
    "Quarter", "Quarter " & QUARTER ( [Date] )
)

     

    Measure = 
VAR t =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Calendar', 'Table' ),
            'Calendar'[Date] >= 'Table'[Start Date]
        ),
        [Stream],
        [Year],
        [Quarter],
        [Salary]
    )
RETURN
    SUMX ( t, [Salary] / 4 )

     

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      THanks Greg. Sorry I should have been more clearer.

      The Start Date represent the starting date of the resource with their annual salary.

      I need a function to determine the quarterly salary of that resource broken down into 4 quarter, starting from quarter his start date falls into.

      Then using that function in a Matrix visual display the above table.

       

      For example, if a resource started in June 2022 with a salary of 100K The table would display

      Stream Q1  Q2    Q3    Q4

      Dev      0$   25K  25K  25K

       

      if he started in july then Q2 would reflect that month.

      thanks again

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

         

        How about this:

        Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "QuarterNum", QUARTER ( [Date] ),
    "Quarter", "Quarter " & QUARTER ( [Date] )
)

         

        Measure = 
VAR t =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Calendar', 'Table' ),
            'Calendar'[Date] >= 'Table'[Start Date]
        ),
        [Stream],
        [Year],
        [Quarter],
        [Salary]
    )
RETURN
    SUMX ( t, [Salary] / 4 )

         

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.