Date diff between 2 columns on 2 different (indirectly) related tables

[mitchpj75]

I have 2 indirectly related tables which contain colmns that i wish to calculate the date difference from in days.

Table "first_reports_live xxorc_vfn_case_attribute_values" with column "Date allocated to volunteer"

Table "first_reports_live xxorc_vfn_case_attribute_values (2)" with column "Outcome date"

I thought it best to use "date diff" to calculate the number of days between the 2 dates (the columns are set as Date type).

The 2 tables are not related directly but are joined via table "first_reports_live xxorc_vfn_reports_cases" in the report.

I am using date diff but the tables are not appearing so I cannot reference them for the columns I need to use!  I just want to know how to put the columns into the formula bar correctly so that I can create a new column to the right of the exitsting collumns display the results of the calcualtion.

I am creating a measure (the best way?) but the measure doesn't allow me to referecne the tables. Should I be using a quick measure (subtraction option?) to work out the days (only seems to allow for counts)?  if a measure, on which table do I set it up on? Should it be a new column?

I was just then going to pull the new measure / new column into the report.

Thanks in advance!

[v-chuncz-msft]

@mitchpj75,

Specify an aggregation such as max to get a single result. You may also use RELATED Function to add calculated columns first.

DATEDIFF ( MAX (

[mitchpj75]

Thanks for coming back to me!

I have tried the RELATED column approach but the 2 tables in question dont appear in the calculation drop down for me to select?

Where is the best place to create the new date diff calculation you mention - on one of the 2 tables I want to use or on the indirectly related table?

You mention a calculated column - is it best to create a new column with the calc in or a new measure - just unsure!

Thanks!

[v-chuncz-msft]

@mitchpj75,

Here is a good article for your reference.