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 you have any solution? 

  • 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.

3 Replies

  • andrewpirie's avatar
    andrewpirie
    Icon for Resolver II rankResolver II
    3 years ago

    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.

  • 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.