Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

multiply two columns with different date

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...
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago
 

 

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's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    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.