Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a pretty straightforward issue involving compairing date data from two different tables.
I am comparing data from two tables.
The Billable Hours by Month/Year has the following structure
The time entry table has an exact date (dd/mm/yyyy), engineer_id, and # of hours worked.
How do I properly link these two tables so I can show that engineer A worked 190 hours in January 2021 of the 168 monthly working hours?
Right now I have a calendar table that sits between these two tables with the following structure
The time entry table links to the 'date' of the calendar table
But I dont know how to link the monthly billable hours table to the calendar table.
I tried to on month but realized since there are two years of data in that table the month was an ambiguous reference point.
Any help would be greatly appreciated!
In the BillableHours table, create this calculated column:
Date = DATE ( BillableHours[Year], BillableHours[Month], 1 )
Then, use this column to create a relationship with the calendar table (date column).
Proud to be a Super User!
Thank you DataInsights! That helped me get on the right track but did not form the relationship I needed.
With that relationship, a visualization that used both tables had issues with aggregates on time entries that were not on the first of the month.
To resolve this issue I was able to format the date column in the 'Monthly Billable Hours' table to be YearMonth [2021-01]. Then on the calendar table I used the simple dax command to add the column for YearMonth to the Autocalendar table generated. This way all time entries of January 2021 were assoicated with the right total of monthly billable hours.
As a note, after posting this question, my company upgraded me from the trial to pro version, and for some reason I needed to recreate my account here, hence the extra _.
Edit: I would like to point out the reason your answer didnt resolve my issue is largely due to the way I posed my question and the lack of information on what I wanted from my visual. I'll try to get the ghost acocunt worked out so this can be marked as the answer. Thank you kindly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |