calculate difference between two separate filtered measure

[Georgetimes]

Hi,

I'm having some issues solving this. I've tried multiple way, none with success.

It's hard to provide you with a data sample, but my tables are as per below:

Main Data - columns that matter are: BookingID, Date, Country, Customer

I have 1 date filter to select a period (i.e 30/06/2024 - 12/07/2024)
I have table 1, with filter1 where I'm selecting customer A, B for example and inside the table I can see by country how many bookings they had as per below:

Same thing for table2, with filter2, however I'm selecting now different customers, customer C,D and I'm seeing for those customers, between the same date period, the number of bookings they had by country:

What I want:
 - I need to see the difference between results from table1 and table 2. If country X doesn't appear in table1, but appears in table2, then show as -100 (table1 minus table2), or if it appears in table 1 but not table2, then 100


Everything that I've tried shows as blank because of those filter by customer (filter1 and filter2) and I can't find a way of displaying just the countries that are relevent for that date period and customer
A small sample created by me would be this:

BookingID	Date	Country	Customer
1	30/06/2024	United Kingdom	A
2	30/06/2024	United Kingdom	A
3	30/06/2024	United Kingdom	B
4	01/07/2024	United Kingdom	A
5	01/07/2024	Belgium	A
6	01/07/2024	United Kingdom	B
7	01/07/2024	United Kingdom	A
8	01/07/2024	Belgium	A
9	02/08/2024	Belgium	B
10	01/01/2023	France	B

[Georgetimes]

Sub ExtractAndProperForename()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim lastRow As Long
Dim header As Range
Dim columnHeaders As Variant
Dim columnsDict As Object
Dim i As Long
Dim forenameColumn As Long
Dim forenameValue As String

' Create a dictionary to hold column headers and their respective column numbers
Set columnsDict = CreateObject("Scripting.Dictionary")

' Define the headers you are looking for
columnHeaders = Array("Forename", "Surname", "Country", "DOB", "Email", "Phone") ' Add more headers if needed

' Reference to the source sheet (adjust if your sheet name is different)
Set wsSource = ThisWorkbook.Sheets("Sheet1")

' Create or reference a target sheet (e.g., "FormattedData")
On Error Resume Next ' In case the sheet doesn't exist
Set wsTarget = ThisWorkbook.Sheets("FormattedData")
On Error GoTo 0 ' Reset error handling

If wsTarget Is Nothing Then
' If "FormattedData" sheet doesn't exist, create it
Set wsTarget = ThisWorkbook.Sheets.Add
wsTarget.Name = "FormattedData"
End If

' Get the last row of data in the sheet
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

' Loop through the first row (header row) to find column numbers for each required header
For Each header In wsSource.Rows(1).Cells
headerName = header.Value
' If the header matches any in the columnHeaders array, add the column number to the dictionary
If Not IsError(Application.Match(headerName, columnHeaders, 0)) Then
columnNumber = header.Column
columnsDict.Add headerName, columnNumber
End If
Next header

' If the dictionary has columns, process each row and extract the data
If columnsDict.Count > 0 Then
' Loop through each row and extract the corresponding data
For i = 2 To lastRow
' Extract Forename value using the dynamically found column
If columnsDict.Exists("Forename") Then
forenameColumn = columnsDict("Forename")
forenameValue = Application.WorksheetFunction.Proper(wsSource.Cells(i, forenameColumn).Value)

' You can now move the Forename to the target sheet
wsTarget.Cells(i, 1).Value = forenameValue ' Move Forename to column A
End If
Next i
Else
MsgBox "No required columns found!", vbExclamation
End If
End Sub

[Georgetimes]

Got it! Here's how we can approach writing a VBA script to create the new Excel file with all the formatting you need. I'll break it down into steps based on your description, and I'll provide the VBA code for each.

### Steps Breakdown:
1. **Customer Reference** – Copy as is.
2. **Full Name** – Combine Forename, Middle Name, and Surname into one column with proper capitalization.
3. **Date of Birth** – Combine the day, month, and year columns into one column in the `DD/MM/YYYY` format.
4. **Address** – Combine the address columns (building number, street, postcode, city, and country) into one column, ensuring capitalization and no extra spaces.
5. **Email and Mobile** – Add after the address.
6. **Codes and Meanings** – Use an IF function to display the appropriate meaning based on the code.
7. **Output** – Create a new sheet with these combined columns in the same order.

---

### VBA Code:

```vba
Sub CreateNewFile()
Dim wsSource As Worksheet
Dim wsNew As Worksheet
Dim lastRow As Long
Dim i As Long
Dim customerRef As String, fullName As String, dob As String, address As String, email As String, mobile As String
Dim code As String, meaning As String
Dim forename As String, middleName As String, surname As String
Dim day As String, month As String, year As String
Dim building As String, street As String, postcode As String, city As String, country As String

' Reference to the original sheet (adjust if your sheet name is different)
Set wsSource = ThisWorkbook.Sheets("Sheet1")

' Create new sheet
Set wsNew = ThisWorkbook.Sheets.Add
wsNew.Name = "FormattedData"

' Get the last row in the source data (assuming data starts from row 2, row 1 being headers)
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

' Loop through each row of data
For i = 2 To lastRow
' 1. Customer Reference
customerRef = wsSource.Cells(i, 1).Value

' 2. Full Name (combine Forename, Middle Name, Surname)
forename = Application.WorksheetFunction.Proper(wsSource.Cells(i, 2).Value)
middleName = Application.WorksheetFunction.Proper(wsSource.Cells(i, 3).Value)
surname = Application.WorksheetFunction.Proper(wsSource.Cells(i, 4).Value)
fullName = forename & " " & middleName & " " & surname

' 3. Date of Birth (combine Day, Month, Year)
day = Format(wsSource.Cells(i, 5).Value, "00")
month = Format(wsSource.Cells(i, 6).Value, "00")
year = wsSource.Cells(i, 7).Value
dob = day & "/" & month & "/" & year

' 4. Address (combine Building, Street, Postcode, City, Country)
building = wsSource.Cells(i, 8).Value
street = wsSource.Cells(i, 9).Value
postcode = wsSource.Cells(i, 10).Value
city = wsSource.Cells(i, 11).Value
country = wsSource.Cells(i, 12).Value
address = Application.WorksheetFunction.Proper(Trim(building & " " & street & " " & postcode & " " & city & " " & country))

' 5. Email and Mobile (get email and mobile)
email = wsSource.Cells(i, 13).Value
mobile = wsSource.Cells(i, 14).Value

' 6. Codes and Meanings (example logic)
code = wsSource.Cells(i, 15).Value
meaning = GetCodeMeaning(code)

' 7. Add the results to the new sheet (order as requested)
wsNew.Cells(i, 1).Value = customerRef
wsNew.Cells(i, 2).Value = fullName
wsNew.Cells(i, 3).Value = dob
wsNew.Cells(i, 4).Value = address
wsNew.Cells(i, 5).Value = email
wsNew.Cells(i, 6).Value = mobile
wsNew.Cells(i, 7).Value = meaning
Next i
End Sub

' Function to get the meaning of a code (can be expanded for more codes)
Function GetCodeMeaning(code As String) As String
Select Case code
Case "A1"
GetCodeMeaning = "Meaning 1"
Case "B2"
GetCodeMeaning = "Meaning 2"
Case "C3"
GetCodeMeaning = "Meaning 3"
Case Else
GetCodeMeaning = "Unknown Code"
End Select
End Function
```

### Explanation:

1. **Customer Reference** – Simply copies the customer reference as it is.
2. **Full Name** – The `PROPER` function is used to ensure the first letter of each name part is capitalized. We combine the forename, middle name, and surname.
3. **Date of Birth** – The day, month, and year columns are combined using `Format` to ensure leading zeros for the day and month. We concatenate them in `DD/MM/YYYY` format.
4. **Address** – The building, street, postcode, city, and country are combined into one string, and `PROPER` ensures the address is properly capitalized. `Trim` ensures no extra spaces.
5. **Email and Mobile** – These are placed after the address.
6. **Codes and Meanings** – The `GetCodeMeaning` function checks the code and returns the corresponding meaning.
7. **New Sheet** – The script creates a new sheet called `"FormattedData"`, and it fills the columns with the formatted data in the order you requested.

---

### How to Use the Code:

1. Press `Alt + F11` to open the VBA editor in Excel.
2. In the editor, click `Insert > Module` to create a new module.
3. Paste the code into this module.
4. Press `F5` or run the macro from Excel to execute it.

This will create a new sheet in the original workbook with the formatted data as specified. If you need to adjust any of the column indexes or logic, feel free to tweak the code. Let me know if you'd like any further modifications or explanations!

[Anonymous]

Hi @Georgetimes ,
You are trying to filter to see the difference between the different customer regions in table 1 and table 2, right, and since I don't understand the structure of your two tables, I'll try to use your example data to try to recreate your problem as best as I can.

BookingsTable1 = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER('Table','Table'[Customer] IN {"A", "B"})
)

BookingsTable2 = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER('Table','Table'[Customer]IN{"C","D"})
)

Difference = 
VAR Table1Bookings = [BookingsTable1]
VAR Table2Bookings = [BookingsTable2]
RETURN
    IF(
        ISBLANK(Table1Bookings) && NOT(ISBLANK(Table2Bookings)),
        -100,
        IF(
            NOT(ISBLANK(Table1Bookings)) && ISBLANK(Table2Bookings),
            100,
            Table1Bookings - Table2Bookings
        )
    )

You can see that I set up two MEASURES to make a judgment, I chose two different customers to compare and also I tried to divide the customers of AB and CD into two tables, but after dividing them into two tables, I could not get the correct country field in the matrix, so I tried to write all the COUNTRY fields in one table and then pass a filter to make a judgment. It can fulfill your requirement.

I hope my thoughts have been helpful, and if you have further questions, feel free to contact me and I'll get back to you the first time I hear from you!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Georgetimes]

Hi,

Thank you very much for taking your time to help me with this, I really appreciate it.

This helps me with a part of my issue, however I'm afraid it's not exactly what I was looking for.

 - the first two tables should show the count of bookingId.  This will be an easy fix, however wanted to mention it in case it will affect the next steps :

                            - in table1 Belgium should show 2, UK 2, France 1 and Italy 1

                            - in table 2 Italy 1, Belgium 1

- I'm not sure if this is correct, but BookingsTable1 and BookingsTable2, both measures might need a different approach as I'm trying to have this fully dynamic. I'm not sure if this will work if I'm selecting for Table1, only customerB, or customer C and D. The "user" will select what Customer he wants to see in table1 and table2 (therefore, the filters). I've tried to play with your PBI example and Table3(the difference table) didn't show the right countries.

I've selected only customer B (table1) and customer D (table2) and it was supposed to display Belgium, France, UK and Italy. Italy was not showing. ( it should show all countries, just once, no matter if it's in both tables or only in one). It looks likeSee screenshot below:

- this leads me to the next thing. The difference table. This should show the booking difference between table1 and table2. As per the above screenshot, it should show:

                 - Belgium 0 (table 1 has 1 booking for Belgium minus table2 which has 1 booking as well)

                 - France 100 (France is not in table2, therefore 100)

                 - UK 100 (Again, UK not in table2, therefore 100)

                 - Italy -100 (this should show as minus 100 as Italy is only in table2, so the calculation would be 0 minus 100)

Hope this makes more sense and thank you very much once again for your help

[Georgetimes]

@Anonymous - any idea about how can I do the above?

Any help is highly appreciated. Thank you!

[Georgetimes]

let
// Function to check if a page has actual data (not just headers)
PageHasData = (PageNumber as number) as logical =>
let
TargetURL = "https://find-and-update.company-information.service.gov.uk/register-of-disqualifications/B?page=" & Number.ToText(PageNumber),
TryPage = try Web.BrowserContents(TargetURL),
Fallback = if TryPage[HasError] then null else TryPage[Value],
PageData = if Fallback = null then null else
Html.Table(Fallback, {{"Check", "TABLE.full-width-table > * > TR > :nth-child(1)"}}, [RowSelector = "TABLE.full-width-table > * > TR"]),
ValidRowCount = if PageData = null then 0 else Table.RowCount(PageData)
in
ValidRowCount > 1, // More than just the header row

// Function to find the last valid page dynamically
FindMaxPage = () =>
let
PageNumbers = List.Numbers(1, 20), // Check up to 20 pages
ValidPages = List.FirstN(PageNumbers, each PageHasData(_)) // Stop when a page is blank
in
if List.Count(ValidPages) = 0 then 1 else List.Last(ValidPages),

MaxPages = FindMaxPage(),

// Function to extract data from a page
GetPage = (PageNumber as number) =>
let
TargetURL = "https://find-and-update.company-information.service.gov.uk/register-of-disqualifications/B?page=" & Number.ToText(PageNumber),
TryPage = try Web.BrowserContents(TargetURL),
Fallback = if TryPage[HasError] then null else TryPage[Value],
PageData = if Fallback = null then null else
Html.Table(Fallback,
{
{"Name", "TABLE.full-width-table > * > TR > :nth-child(1)"},
{"DOB", "TABLE.full-width-table > * > TR > :nth-child(2)"},
{"Town", "TABLE.full-width-table > * > TR > :nth-child(3)"}
},
[RowSelector = "TABLE.full-width-table > * > TR"]
)
in
PageData,

// Generate only the valid pages dynamically
PageNumbers = List.Numbers(1, MaxPages),

// Fetch and process data from valid pages
AllPagesData = List.Transform(PageNumbers, each GetPage(_)),
CombinedData = List.RemoveNulls(AllPagesData),
AppendData = Table.Combine(CombinedData),
// Clean and transform data
#"Removed Top Rows" = Table.Skip(AppendData, 1),
#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each not Text.Contains([Name], "Name (of dis")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows", {{"DOB", type date}})
in
#"Changed Type"