Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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.
Proud to be a Super User!
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
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
Proud to be a Super User!
@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]) ))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
82 | |
65 | |
63 | |
57 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |