topic Re: multiply two columns with different date in DAX Commands and Tips

multiply two columns with different date

Anonymous — Sat, 25 Mar 2023 11:28:39 GMT

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?

Re: multiply two columns with different date

andrewpirie — Sat, 25 Mar 2023 19:47:18 GMT

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.

Re: multiply two columns with different date

Anonymous — Mon, 27 Mar 2023 10:07:56 GMT

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

Re: multiply two columns with different date

v-jingzhang — Tue, 28 Mar 2023 07:15:01 GMT

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.