Sum the month, when only one day is selected

[SH-4]

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?

[SH-4 · Accepted solution]

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

[v-rzhou-msft]

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.