Forum Discussion
Help comparing dates between tables
Hi there, I am hoping someone can help me with this. Have spent hours and hours looking through the solutions posted and cant figure out.
I have two different systems for people to enter timesheet. Lets call them A and B. Once a month I do a leave reconciliation and need to check that the leave was entered on both systems for the same date.
As shown below what happens is Mary might enter annual leave on 1st of May on system A but not on System B. So when I calculate the difference between both systems, Mary will be showing -2 but I dont know which days are missing. Note that we will have entries for the entire year so Mary will have several entries per year. I can get the graphic representation showing tha on system A she has 4 days entered, and on System B she has 2, but I can't figure out which date is missing on System B.
Also important to note that the date on System B is mandatory, i.e., people must have entries on that system. so I need a way to show to people which days that has been imput on System A that was not entered on System B.
Hoping you can help.
19 Replies
Use CONCATENATEX(EXCEPT( <filtered dates from A>, <filtered dates from B>),[Date],",")
Thanks Greg, I will try this one now to see if it works. Will report back (though not sure I understand how the filtered dates work but will give it a go). Thanks
rdops - If you have the names of the person in a visual then you could do something like:
VAR __Name = MAX('Table'[Name])
CONCATENATEX(
EXCEPT(
SELECTCOLUMNS(FILTER('Table1',[Name]=__Name),"Date",[Date]))
SELECTCOLUMNS(FILTER('Table2',[Name]=__Name),"Date",[Date]))
),
[Date],
","
)
So, basically, get me all the dates from table 1 and take away all of the dates from table 2 so now the ones left are the missing ones.
A couple of things you can try:
Either do a MERGE in the Power Query Editor and choose LEFT ANTI as the Join Kind. If you Merge as new table, this will give you a 3rd table that will show the values in A that are not in B. Before you do this you will need to create a new merged column that has all the data that should be the same in each table, so in your case all 4 columns displayed.
Or you could use dimension DATE, Employee and Leave Type tables in the Power BI report that can link the two tables and try to reconcile that way by using the columns from the dimension tables and create a measure that calculates the difference in hours between source A and B. Add this as a filter to the visualization and filter for when the difference is not equal to 0.
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.
Thanks for your reply, Allison.
I tried this solution already but I must be doing something wrong as I dont see the result that I want. I am fairly new to Power BI and have no programming background so most of the times I just copy and paste the formulas that are posted here and if there is one step not described I will miss and therefor the solution wont work. I created the column with the employee's name, which is common to both tables, still didn't work.
Would you mind elaborating a bit more on how to use dimension Date?. I already have the calculated hours difference between the two tables. Thanks a lot.
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):
- Click on Transform Data to open the Power Query Editor.
- Select Sample A and use the Shift key to select all four columns. Click Add Column tab in the ribbon and choose Merge.
- Repeate Step 2 with Sample B.
- In the Home tab in the ribbon, select the down arrow near Merge Queries and choose Merge Queries as New.
- 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)
- Click on the 'Merged' column in both.
- 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/)
- Click OK.
- 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:
- 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/
- 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.
- Load all 5 tables into Power BI (DimDate, SourceA, SourceB, Employee, LeaveType)
- Check the relationships:
- Relate DimDate to SourceA using [Date] column.
- Relate Employee to SourceA using [Name]
- Relate LeaveType to SourceA using [LeaveType]
- Relate DimDate to SourceB using [Date] column.
- Relate Employee to SourceB using [Name]
- Relate LeaveType to SourceB using [LeaveType]
- All relationships should be 1 to many.
- Create a few MEASURES:
- SouceATotalHours=SUM(SourceA[Hours])
- SourceBTotalHours=SUM(SourceB[Hours])
- DifferenceHours=[SourceATotalHours]-[SourceBTotalHours]
- Last step is to get the visualization right, because MEASURES change value depending on the context of the visualization they're place in. So:
- Put Employee[Name], LeaveType[Type], DimDate[Date] into Rows for a Matrix visualization.
- Put DifferenceHours in the values for that same matrix.
- 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.
Hi rdops ,
What's your expected result?
Not sure if i understand you correctly, but i used EXCEPT() function and below is the result.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your erply V-Jay. What I want is to know which dates are in system A but not in System B, which is exactly what you got there. Not sure why I cant make it work. Will try it again now using your except formula.
Thanks again.
Cheers
Hi rdops ,
Click the New Table feature under Modeling then paste the formula to the input box, that should work.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jay, tried yours but didnt work because the two systems are not as clean/simple as the two examples below.
I have about 20 different fields on each table/system. Is just that I only need the missing date in system B.
I used the formular you posted and got an error; I assume I need to tell the tool whych column on each table I want to compare.
I tried the exceptx(filtered option and got nowhere.
rdops try this DAX as a new TABLE from the modeling tab in the report:
Table = EXCEPT(SUMMARIZE(SourceA,SourceA[Name],SourceA[Start date],SourceA[Leave type], SourceA[Hours]),SUMMARIZE(SourceB,SourceB[Name],SourceB[Start date],SourceB[Leave type], SourceB[Hours]))Hi Allison, I did this but it shows exactly the same thing that is on Source A. I mean, this table displays exactly the same as if I had a table for Sorce A (without any DAX).
and I am officially done with this. Just cant make it work. Nothing that I do works. I cannot even look at this report anymore.
Also for the records, the number of columns and fields are different on each table so is not as straight forward. Anyway, I am done.
Thanks everyone for all your help.
