topic Re: Sum the month, when only one day is selected in DAX Commands and Tips

Sum the month, when only one day is selected

SH-4 — Thu, 27 Apr 2023 14:03:29 GMT

So I have this formula in Excel, which looks at the month number I have above my date to sum up an entire row that matches that month number in order to determine which rate I want to multiply that day's units by, since they base the rates off the monthly sum.

=IF(SUMIF('X'!4:4, 4, 'X'!27:27)>=365000, (CS31*0.077),

IF(SUMIF('X'!4:4, 4, 'X'!27:27)>=243000, (CS31*0.095),

IF(SUMIF('X'!4:4, 4, 'X'!27:27)>=162000, (CS31*0.117),

IF(SUMIF('X'!4:4, 4, 'X'!27:27)>=121000, (CS31*0.15),

IF(SUMIF('X'!4:4, 4, 'X'!27:27)>=80000, (CS31*0.2),

IF(SUMIF('X'!4:4, 4, 'X'!27:27)>=1, (CS31*0.5),

0))))))

I'm trying to get this to work in DAX, when only one day is selected, but I can't get it to look at the whole month's numbers to sum when only one day is selected. Does anyone have any experience with trying to get this to work?

Re: Sum the month, when only one day is selected

SH-4 — Thu, 27 Apr 2023 14:53:19 GMT

okay actually... I figured it out! if anyone needs it, I used a combination of TOTALMTD() and DATEMTD()

Units MTD = TOTALMTD((CALCULATE(SUM('X'[QTY]), 'X'[CLIENT_ID]="Client", 'X'[ORDTYP (groups)]="Normal")), DATESMTD('calendar'[Date]))

unit shipped revenue =

IF([Units MTD]>=365000, ([Outbound Units X]*[unit 365k rate]),

IF([Units MTD]>=243000, ([Outbound Units X]*[unit 243k rate]),

IF([Units MTD]>=162000, ([Outbound Units X]*[unit 162k rate]),

IF([Units MTD]>=121000, ([Outbound Units X]*[unit 121k rate]),

IF([Units MTD]>=80000, ([Outbound Units X]*[unit 80k rate]),

IF([Units MTD]>=1, ([Outbound Units X]*[unit 1 rate]),

0))))))

Re: Sum the month, when only one day is selected

Anonymous — Mon, 01 May 2023 08:59:18 GMT

Hi SH-4 ,

I'm so glad you have solved your issue. And thanks for sharing this method for us to solve this issue.

Please mark your reply as a solution. More people will benefit from it.

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.