Help comparing dates between tables

rdops  If I understand you correctly, you've calculated the hours difference between two tables based on name only. You need to base this on Name AND Date (and type too I think?)

So my Power Query Editor solution (similar to Greg_Deckler 's solution but using Power Query instead of DAX): 

1. Click on Transform Data to open the Power Query Editor.
2. Select Sample A and use the Shift key to select all four columns. Click Add Column tab in the ribbon and choose Merge.
3. Repeate Step 2 with Sample B.
4. In the Home tab in the ribbon, select the down arrow near Merge Queries and choose Merge Queries as New. 
5. Select the Sample A and Sample B for the tables (order does matter, the top table should be the one that has all the results and the bottom table should be the one that only has some of the results)
6. Click on the 'Merged' column in both.
7. Choose Left Anti as the Join Kind. This will take the top table (Sample A) and remove any rows from it that are also in Sample B, but keep everything else. (see this post for an example: https://www.poweredsolutions.co/2019/01/10/merge-operations-in-power-bi-power-query-part-3-left-anti-join/) 
8. Click OK. 
9. You'll be left with a table that contains a funny looking column full of 'table'. You can delete that column and load this to the report. 

For my DimDate solution: 

1. Create a DimDate table. Here's one option for how to do that: https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
2. Do you already have an Employee table and a Leave Type table? If not, you should now that you are working with comparing two source tables. You can do this in Excel by just creating a simple table that has each employee listed exactly once, and another table that has each leave type listed exactly once. 
3. Load all 5 tables into Power BI (DimDate, SourceA, SourceB, Employee, LeaveType)
4. Check the relationships:
   1. Relate DimDate to SourceA using [Date] column.
   2. Relate Employee to SourceA using [Name]
   3. Relate LeaveType to SourceA using [LeaveType]
   4. Relate DimDate to SourceB using [Date] column.
   5. Relate Employee to SourceB using [Name]
   6. Relate LeaveType to SourceB using [LeaveType]
   7. All relationships should be 1 to many.
5. Create a few MEASURES:
   1. SouceATotalHours=SUM(SourceA[Hours])
   2. SourceBTotalHours=SUM(SourceB[Hours])
   3. DifferenceHours=[SourceATotalHours]-[SourceBTotalHours]
6. Last step is to get the visualization right, because MEASURES change value depending on the context of the visualization they're place in. So:
   1. Put Employee[Name], LeaveType[Type], DimDate[Date] into Rows for a Matrix visualization.
   2. Put DifferenceHours in the values for that same matrix.
   3. You should only see rows that are not equal to 0. Note if the number hours is negative: this method will also display rows that are only in SourceB but not in SourceA if that somehow were to happen (though I believe you said that's not possible).

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv