Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Matrix by quarters

        Hey guys,   I have the following data table: Stream Start Date Salary Dev Jan 1, 2022 100,000 Sales Mar 3, 2022 120,000 Sales July 1, 2022 100,000 PM Oct 10...
  • 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.

  • 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.