calculate difference between two separate filtered measure

Question

Hi,&nbsp;I'm having some issues solving this. I've tried multiple way, none with success.&nbsp;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, CustomerI 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:&nbsp;&nbsp;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:&nbsp;&nbsp;What I want:&nbsp;- 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 100Everything 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 customerA small sample created by me would be this:BookingIDDateCountryCustomer130/06/2024United KingdomA230/06/2024United KingdomA330/06/2024United KingdomB401/07/2024United KingdomA501/07/2024BelgiumA601/07/2024United KingdomB701/07/2024United KingdomA801/07/2024BelgiumA902/08/2024BelgiumB1001/01/2023FranceB&nbsp;

anonymous · Answer

Hi&nbsp;Georgetimes&nbsp;,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) &amp;&amp; NOT(ISBLANK(Table2Bookings)),
        -100,
        IF(
            NOT(ISBLANK(Table1Bookings)) &amp;&amp; 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!
&nbsp;
Best regards,Community Support Team_ Tom Shen
&nbsp;
If this post helps then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;

georgetimes · Answer

Hi,&nbsp;Thank you very much for taking your time to help me with this, I really appreciate it.&nbsp;This helps me with a part of my issue, however I'm afraid it's not exactly what I was looking for.&nbsp;&nbsp;- the first two tables should show the count of bookingId.&nbsp; This will be an easy fix, however wanted to mention it in case it will affect the next steps :&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&nbsp;in table1 Belgium should show 2, UK 2, France 1 and Italy 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; - in table 2 Italy 1, Belgium 1&nbsp;- 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:&nbsp;- 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:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;- Belgium 0 (table 1 has 1 booking for Belgium minus table2 which has 1 booking as well)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;- France 100 (France is not in table2, therefore 100)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;- UK 100 (Again, UK not in table2, therefore 100)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;- Italy -100 (this should show as minus 100 as Italy is only in table2, so the calculation would be 0 minus 100)&nbsp;&nbsp;Hope this makes more sense and thank you very much once again for your help&nbsp;

georgetimes · Answer

Anonymous&nbsp;- any idea about how can I do the above?&nbsp;Any help is highly appreciated. Thank you!

georgetimes · Answer

let// Function to check if a page has actual data (not just headers)PageHasData = (PageNumber as number) as logical =&gt;letTargetURL = "https://find-and-update.company-information.service.gov.uk/register-of-disqualifications/B?page=" &amp; Number.ToText(PageNumber),TryPage = try Web.BrowserContents(TargetURL),Fallback = if TryPage[HasError] then null else TryPage[Value],PageData = if Fallback = null then null elseHtml.Table(Fallback, {{"Check", "TABLE.full-width-table &gt; * &gt; TR &gt; :nth-child(1)"}}, [RowSelector = "TABLE.full-width-table &gt; * &gt; TR"]),ValidRowCount = if PageData = null then 0 else Table.RowCount(PageData)inValidRowCount &gt; 1, // More than just the header row// Function to find the last valid page dynamicallyFindMaxPage = () =&gt;letPageNumbers = List.Numbers(1, 20), // Check up to 20 pagesValidPages = List.FirstN(PageNumbers, each PageHasData(_)) // Stop when a page is blankinif List.Count(ValidPages) = 0 then 1 else List.Last(ValidPages),MaxPages = FindMaxPage(),// Function to extract data from a pageGetPage = (PageNumber as number) =&gt;letTargetURL = "https://find-and-update.company-information.service.gov.uk/register-of-disqualifications/B?page=" &amp; Number.ToText(PageNumber),TryPage = try Web.BrowserContents(TargetURL),Fallback = if TryPage[HasError] then null else TryPage[Value],PageData = if Fallback = null then null elseHtml.Table(Fallback,{{"Name", "TABLE.full-width-table &gt; * &gt; TR &gt; :nth-child(1)"},{"DOB", "TABLE.full-width-table &gt; * &gt; TR &gt; :nth-child(2)"},{"Town", "TABLE.full-width-table &gt; * &gt; TR &gt; :nth-child(3)"}},[RowSelector = "TABLE.full-width-table &gt; * &gt; TR"])inPageData,// Generate only the valid pages dynamicallyPageNumbers = List.Numbers(1, MaxPages),// Fetch and process data from valid pagesAllPagesData = 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"

georgetimes · Answer

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!

Forum Discussion

calculate difference between two separate filtered measure

7 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

1. How to add two different calculated columns of two different table

Calculating Differences

Grouping 2 columns separately for a calculation

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