Calculation between two tables using max date

[jcastr02]

I have two tables below.  I am trying to create a calculated column BY STORE for the previous 43 days.  However, typically Table 2 is always one day behind so I'd like for it to go back 43 days of whenever both tables have the same max date.  In this case, we would go back 43 days from 1/2/2025, since 1/3/2025 hasn't loaded in table 2.  

Then based off those dates 

Do Calculation:     
Sum of Shipped   / (Sum of Shipped+ Sum of Product Reviewed)

Then group by store so that end values are one column for the store and one value for the calculation above.

Example below

Table 1		Table 2
Date	Store#	Shipped		Date	Store#	Product Reviewed
1/1/2025	345	25		1/1/2025	345	6
1/1/2025	899	10		1/1/2025	899	5
1/1/2025	663	2		1/1/2025	663	4
1/2/2025	345	66		1/2/2025	345	5
1/2/2025	899	22		1/2/2025	899	3
1/2/2025	663	11		1/2/2025	663	1
1/3/2025	345	11
1/3/2025	899	12
1/3/2025	663	5

[v-karpurapud]

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.

[v-karpurapud]

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

[DataNinja777]

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,

[lbendlin · Accepted solution]