Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ade_kurniawan
Frequent 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
BeaBF
Impactful Individual
Impactful Individual

@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
Frequent Visitor

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

ade_kurniawan_0-1632307885665.png

 

BeaBF
Impactful Individual
Impactful Individual

@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

BeaBF
Impactful Individual
Impactful Individual

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

 

 

 

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

BeaBF
Impactful Individual
Impactful Individual

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

pbi_comm.png

 

What's wrong with you?

B.

BeaBF
Impactful Individual
Impactful Individual

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

 

Thx,

B.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors