multiply two columns with different date

[Anonymous]

Hello, I have two columns. One column is based on the last month value and another one is based on next month value. 

I want to multiply these two coulmns and sort it based on the current date. 

Do you have any solution? 

[Anonymous]

I want multiply "12 month Retention rate for 202302" into "Total Crew Needed", But as you can see, there is no data for "12 month Retention rate for 202302" , since they are for another date (last month).

[v-jingzhang · Accepted solution]

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.

[andrewpirie]

Could you show a few rows of a sample dataset, and the resulting dataset you're trying to achieve? I have a couple of things I'm not clear on, and a sample could help with this.