Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Zackary_Smith
New Member

Comparing Dates from Two tables - Month based date against day based date

I have a pretty straightforward issue involving compairing date data from two different tables.

I am comparing data from two tables.

  1. Billable Hours by month/year
  2. Time entries against individual dates

 

The Billable Hours by Month/Year has the following structure

Zackary_Smith_0-1647889579431.png


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

Zackary_Smith_2-1647890179125.png

 


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!

2 REPLIES 2
DataInsights
Super User
Super User

@Zackary_Smith,

 

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).





Did I answer your question? Mark my post as a solution!

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.