DAX Formula Needed To Calculate Variance Between Related Rows Using Semi-Complex Logic

[krichmond]

We created a designator field called "Test Indicator" which populates True or False next to related rows if there is a test included. Below is how this works and we have tested it and it works awesome. But we need an additional step to be added now, see below this screenshot for more details.

What we need to do now is create a new DAX field that finds when "True" is present in the "Test Indicator" field for a particular Client ID and Start Date. Example of what that would look like below.

Once the new DAX field identifies the correct rows from the step above, it would then need to take the value from the "Actual Paid Responders" field on the Version Type "TEST" row and subtract it from either the "Actual Paid Responders" field on the Version Type "MATCH PANEL" row or the "Actual Paid Responders" field on the Version Type "CONTROL" row.

If a row with a Version Type of "MATCH PANEL" exists in the bundle of records, it should be used over a row with a Version Type of "CONTROL". However, if a row doesn't exist with a Version Type of "MATCH PANEL" then the row with a Version Type of "CONTROL" should be used. The key to picking the correct "CONTROL" or "MATCH PANEL" row is to always select the one that is closest in Marketing Quantity to the "Marketing Quantity" on the "TEST" row. Sometimes there are multiple tests and corresponding controls and/or match panels so this will ensure those are handled properly.

 Using the screenshots below, the following would be the expected results:

• Client ID 102021995 for March 18, 2022... Test 96 - Control 137 = -41
  • TEST row has a Marketing Quantity of 39,894 and CONTROL row has a Marketing Quantity of 39,895.

• Client ID 102023472 for February 25, 2022... Test 48 - Match Panel 116 = -68
  • TEST row has a Marketing Quantity of 39,887 and MATCH PANEL row has a Marketing Quantity of 39,888.

[lbendlin]

Please provide sample data that covers your issue or question completely, leaving out details that are not relevant to the question.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...