topic Matrix by quarters in DAX Commands and Tips

Matrix by quarters

Anonymous — Wed, 18 May 2022 17:28:59 GMT

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

Re: Matrix by quarters

Greg_Deckler — Wed, 18 May 2022 17:09:10 GMT

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

Re: Matrix by quarters

Anonymous — Wed, 18 May 2022 17:27:49 GMT

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

Re: Matrix by quarters

Icey — Mon, 23 May 2022 06:38:19 GMT

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.