multiply two columns with different date

Hi Anonymous 

Is your "12 month Retention rate for 202302" from another source table or in the same source table as "Total Crew Needed"? Anyway you need to create a measure to get the last month's Retention rate.

Assume that your [Date] column in the matrix visual is from 'Table1' and that your "12 month Retention rate" is from a table 'Table2' which has a month column (with month value like 202302), you can create a measure similar to below:

last month rate =
VAR _lastMonth =
    YEAR ( EOMONTH ( MAX ( 'Table1'[Date] ), -1 ) ) * 100 + MONTH ( EOMONTH ( MAX ( 'Table1'[Date] ), -1 ) )
RETURN
    CALCULATE (
        MAX ( 'Table2'[Retention rate] ),
        ALL ( 'Table2' ),
        'Table2'[Month] = _lastMonth
    )

Add this measure to the matrix visual to check whether it gets the retention rate of last month correctly. Then you can create another measure to multiply it to "Total Crew Needed".

Measure = [last month rate] * SUM ( 'Table'[Total Crew Needed] )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.