Calculation between two tables using max date

Hi jcastr02 ,

To achieve the desired result, first create a DateTable that spans the full range of dates in your data using the following DAX:

DateTable = CALENDAR(MIN('Table1'[Date]), MAX('Table1'[Date]))

Next, define a measure to determine the latest common date between both tables, since Table2 often lags behind by a day. This ensures that the 43-day window only includes dates that exist in both tables:

MaxCommonDate = 
CALCULATE (
    MAX ( 'Table1'[Date] ),
    INTERSECT (
        SELECTCOLUMNS ( 'Table1', "Date", 'Table1'[Date] ),
        SELECTCOLUMNS ( 'Table2', "Date", 'Table2'[Date] )
    )
)

Now you can create the main measure that filters both tables to only include data from the last 43 days up to the max common date. It then calculates the sum of shipped and product reviewed values by store and returns the ratio of shipped to the total of shipped plus reviewed:

Shipped_vs_Reviewed = 
VAR MaxDate = [MaxCommonDate]
VAR StartDate = MaxDate - 42
VAR FilteredShipped =
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[Date] >= StartDate && 'Table1'[Date] <= MaxDate
    )
VAR FilteredReviewed =
    FILTER (
        ALL ( 'Table2' ),
        'Table2'[Date] >= StartDate && 'Table2'[Date] <= MaxDate
    )
VAR ShippedByStore =
    CALCULATE (
        SUM ( 'Table1'[Shipped] ),
        FilteredShipped
    )
VAR ReviewedByStore =
    CALCULATE (
        SUM ( 'Table2'[Product Reviewed] ),
        FilteredReviewed
    )
RETURN
    DIVIDE (
        ShippedByStore,
        ShippedByStore + ReviewedByStore
    )

This measure can then be added to a table or matrix visual with Store# on rows to display the result for each store.

Best regards,