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,

Hi jcastr02 

Could you please confirm if your query have been resolved the solution provided by lbendlin and DataNinja777 ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

Hi jcastr02 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.