Matrix by quarters

[antoinetohme]

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, 2022	140,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):

Stream	Quarter 1	Quarter 2	Quarter3	Quarter 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

[Greg_Deckler]

@antoinetohme You can use Quarter in a calculated column: Quarter - Microsoft Power BI Community

[antoinetohme]

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 · Accepted solution]

Hi @antoinetohme ,

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.