cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ade_kurniawan
Regular Visitor

Sum Only for Selected Month Number YTD

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 :

ade_kurniawan_0-1632286992204.png

Here is the data :

yearmonthforecastactual
2020177
20202105
202031010
2020488
2020567
2020657
2020777
2020869
20209108
20201059
202011710
202012910
2021179
20212610
20213910
20214106
20215106
2021696
2021787
2021859
202199 
2021105 
2021115 
20211210 

 

Thanks for help..

1 ACCEPTED SOLUTION

@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!
B.
 
 

 

 

 

View solution in original post

7 REPLIES 7
ade_kurniawan
Regular Visitor

@bf thanks for response, here is result i want :

ade_kurniawan_0-1632307885665.png

 

@ade_kurniawan 

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!

B.

 

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

@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!
B.
 
 

 

 

 

View solution in original post

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

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

pbi_comm.png

 

What's wrong with you?

B.

bf
Resolver I
Resolver I

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

 

Thx,

B.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!