topic formula to get the last month total and then divide by 3 in DAX Commands and Tips

formula to get the last month total and then divide by 3

WNelson — Thu, 16 Nov 2023 20:32:56 GMT

I want to find the total number for last month and then divide it by 3. I need help with a formual for that.

Re: formula to get the last month total and then divide by 3

oritam3210 — Thu, 16 Nov 2023 21:22:53 GMT

measure_name =
VAR maxMonth = MAX(YourTableName[Start (UTC) Month])
VAR sumLastMonth = CALCULATE(SUM(YourTableName[Total]), YourTableName[Start (UTC) Month] = maxMonth )
RETURN DIVIDE(sumLastMonth ,3)

Here's a breakdown of what this formula does:
1. VAR maxMonth = MAX(YourTableName[Start (UTC) Month])` determines the most recent month in your data.
2. VAR sumLastMonth = CALCULATE(SUM(YourTableName[Total]), YourTableName[Start (UTC) Month] = maxMonth ) calculates the total for the items in the last month.
3. RETURN DIVIDE(sumLastMonth, 3) divides the total sum from the last month by 3 and returns this value.

Make sure to adjust the column names like `Start (UTC) Month` and `Total` to match the columns in your table.

Re: formula to get the last month total and then divide by 3

Anonymous — Mon, 20 Nov 2023 06:28:09 GMT

Hi WNelson ,

Last Month total / 3 = VAR _LASTMONTHSTART = EOMONTH(TODAY(),-2)+1 VAR _LASTMONTHTOTAL = CALCULATE(SUM('Table'[Total]),FILTER('Table','Table'[Start (UTC) Month] = _LASTMONTHSTART)) RETURN DIVIDE(_LASTMONTHTOTAL,3)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: formula to get the last month total and then divide by 3

WNelson — Wed, 22 Nov 2023 15:49:23 GMT

Thank you oritam3210