topic Re: Sum Only for Selected Month Number YTD in DAX Commands and Tips

Sum Only for Selected Month Number YTD

ade_kurniawan — Wed, 22 Sep 2021 05:07:35 GMT

Dear friends,

i have question, how can i sum actual data only month 1 to 8 within 2020 & 2021 using DAX. Here is the table :

Here is the data :

Thanks for help..

BeaBF — Wed, 22 Sep 2021 09:41:07 GMT

ade_kurniawan Hello,
can you give me the expected result? in order to be sure you understand the request.

Thx,

ade_kurniawan — Wed, 22 Sep 2021 10:51:42 GMT

BeaBF thanks for response, here is result i want :

BeaBF — Wed, 22 Sep 2021 11:04:41 GMT

Here are the two measures, to be included in the new table with the column year:

Sum_Actual = CALCULATE(SUM(Tabella[actual]), Tabella[month] >= 1 && Tabella[month] <= 8, ALLEXCEPT(Tabella,Tabella[year]))

Sum_Forecast = CALCULATE(SUM(Tabella[forecast]), Tabella[month] >= 1 && Tabella[month] <= 8, ALLEXCEPT(Tabella,Tabella[year]))

Try and tell me if it works!

ade_kurniawan — Wed, 22 Sep 2021 11:18:01 GMT

How about find number 8 automatically ? when data updated at month 9, i hope will automatically update sum month 1 to 9.. thanks

BeaBF — Thu, 23 Sep 2021 09:03:35 GMT

ade_kurniawan Oh sorry, I didn't understand. Here are the correct measures:

Sum_Actual =

VAR MAXXX = CALCULATE(MIN(Tabella[month]) -1, FILTER (ALL(Tabella),

Tabella[actual] = BLANK () || Tabella[forecast] = BLANK()))

RETURN CALCULATE(SUM(Tabella[actual]), Tabella[month] >= 1 && Tabella[month] <= MAXXX, ALLEXCEPT(Tabella,Tabella[year]))

Sum_Forecast =

VAR MAXXX = CALCULATE(MIN(Tabella[month]) -1, FILTER (ALL(Tabella),

Tabella[actual] = BLANK () || Tabella[forecast] = BLANK()))

RETURN CALCULATE(SUM(Tabella[forecast]), Tabella[month] >= 1 && Tabella[month] <= MAXXX, ALLEXCEPT(Tabella,Tabella[year]))

I hope it is the desired!

ade_kurniawan — Thu, 23 Sep 2021 01:33:33 GMT

Sir, its not working. the result of Var MAXXX is not 8. please help.. thanks

BeaBF — Thu, 23 Sep 2021 07:15:41 GMT

ade_kurniawan for me it's working. Let's see:

What's wrong with you?