Dax function

[antoinetohme]

Hey guys,

I have the following data table:

Stream	Start Date	Annual 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 vizualization like this, where I can calculate the total salary of each resource  by quarter based on their annual 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 each quarter salary portion of that resource broken down into quarters, starting from the 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$   8,333K  25K  25K

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

Stream Q1  Q2     Q3           Q4

Dev      0$   0$      16,66K    25K

Your help is appreicated.

Thank you

[danextian]

Hi @antoinetohme,

I think for your use case, it would be simpler to compute for the daily salary instead of dividing the salary by 4 to get the quarterly rate as not all start dates fall in the first day of the month and not all months have the same number of days.  This can easily done using Power Query. This approach will make your DAX calculations simpler.

Here's a sample M Script that demostrates what I just mentioned.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktU9JR8krMUzDUUTAyMDIC8gwNDHQMDAyUYnWilYITc1KLgWK+iUUKxggVRpgqvEpzKnEYEuALFPBPLlEwNEDImyDkoY4oRXMEWDoWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Stream = _t, #"Start Date" = _t, #"Annual Salary" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Stream", type text}, {"Start Date", type date}, {"Annual Salary", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Days to Next Year", each let
start = [Start Date],
end = #date(Date.Year(start) + 1, Date.Month(start), Date.Day(start))
in
Number.From( end - start) + 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Dates", each List.Dates([Start Date], [Days to Next Year], #duration(1, 0, 0, 0) )),
    #"Inserted Division" = Table.AddColumn(#"Added Custom1", "Daily Salary to Year End", each [Annual Salary] / [Days to Next Year], type number),
    #"Expanded Dates" = Table.ExpandListColumn(#"Inserted Division", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}})
in
    #"Changed Type1"

And here's how it can look in the report view.

[antoinetohme]

Thank you @danextian 

What DAX function did you use to get that report view?

Also are you able to group the report by stream and sum up their salaries?

Thanks again

[danextian]

Hi @antoinetohme ,

I didn't create any calculated column or measure.  Here's a sample pbix for your reference -https://drive.google.com/file/d/1VoQ0wf67_r_Hat1vKxsnuPfIEBR5AoZU/view?usp=sharing 

[amitchandak]

@antoinetohme , Create a date table and do not join it with this table , use qtr from date table in visual

Calendar that starts with any Standard Month — Just one variable apart https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...

Then try a measure like

calculate( Sumx(Table, Datediff(Table[Start Date], Max('Date'[Date]), Month) *[Annual Salary]/12), filter(Table, Table[Date] >= MIN('Date'[Date]) ))