cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
var_dby
Frequent Visitor

Want to sum Quota for year dynamically it should add the sales from every month field

Hi All,

 

I have Quota table in which i have sales for every month(Jan, Feb etc) in this table but the problem is the sales is available in every month fields(below snapshot FYR),

JanFebMarAprMayJunJulAugSepOctNov
23456789101112
22222222222
345678910111213
455587453444444433

So i want to calculate these sales dynamically and it should pick the current month column when month changes, How we can achieve this. Plz suggest.

I am using the DAX for manual calculation till october but when it comes to Nov this should be dynamic-

QuotaYTD = SUMX(Quota, Quota[jan]+Quota[feb]+Quota[mar]+Quota[apr]+Quota[may]+Quota[jun]+Quota[jul]+Quota[aug]+Quota[sep]+Quota[oct])

 

 

 

1 ACCEPTED SOLUTION

Hi @var_dby 

 

it is no exactly dynamic, I explain it better.

 

In the formula you must type all the months of the year:

QuotaYTD =
VAR varJan = sum(Hoja1[Jan])
VAR varFeb = sum(Hoja1[Jan]) + sum(Hoja1[Feb])
...
VAR varOct = sum(Hoja1[Jan]) + sum(Hoja1[Feb]) + ... + sum(Hoja1[Oct])
VAR varNov = sum(Hoja1[Jan]) + sum(Hoja1[Feb]) + ... + sum(Hoja1[Oct]) + sum(Hoja1[ONov])
VAR varDec = sum(Hoja1[Jan]) + sum(Hoja1[Feb]) + ... + sum(Hoja1[Oct])+ sum(Hoja1[ONov]) + sum(Hoja1[Dec])
RETURN
SWITCH(MONTH(TODAY());
1; varJan;
2; varFeb;
...
10; varOct;
11; varNov;
12; varDec;
0
)
 
So, when month(today()) matches with the number in the list it will return the variable corresponding to that month.
 
The only problem I find is what will happen if a month does not exist in your table, so perhaps you have to rewrite the formula writting ifblanksum(Hoja1[Oct]), 0) for each month.

View solution in original post

6 REPLIES 6
josemanuelcayon
Helper II
Helper II

Hi @var_dby 

 

Try to create the following measure:

 

QuotaYTD =
VAR varJan = sum(Hoja1[Jan])
VAR varFeb = sum(Hoja1[Jan]) + sum(Hoja1[Feb])
...
VAR varOct = sum(Hoja1[Jan]) + sum(Hoja1[Feb]) + ... + sum(Hoja1[Oct])
RETURN
SWITCH(MONTH(TODAY());
1; varJan;
2; varFeb;
...
10; varOct;
0
)
 
I tried it and it works

Hi @josemanuelcayon 

 

Can you please tell me if month changes to November so this will work or not because I want to add them dynamically when month changes..

 

Thanks..

Hi @var_dby 

 

it is no exactly dynamic, I explain it better.

 

In the formula you must type all the months of the year:

QuotaYTD =
VAR varJan = sum(Hoja1[Jan])
VAR varFeb = sum(Hoja1[Jan]) + sum(Hoja1[Feb])
...
VAR varOct = sum(Hoja1[Jan]) + sum(Hoja1[Feb]) + ... + sum(Hoja1[Oct])
VAR varNov = sum(Hoja1[Jan]) + sum(Hoja1[Feb]) + ... + sum(Hoja1[Oct]) + sum(Hoja1[ONov])
VAR varDec = sum(Hoja1[Jan]) + sum(Hoja1[Feb]) + ... + sum(Hoja1[Oct])+ sum(Hoja1[ONov]) + sum(Hoja1[Dec])
RETURN
SWITCH(MONTH(TODAY());
1; varJan;
2; varFeb;
...
10; varOct;
11; varNov;
12; varDec;
0
)
 
So, when month(today()) matches with the number in the list it will return the variable corresponding to that month.
 
The only problem I find is what will happen if a month does not exist in your table, so perhaps you have to rewrite the formula writting ifblanksum(Hoja1[Oct]), 0) for each month.

View solution in original post

Hi @josemanuelcayon 

 

Thanks for your reply, For now it is working fine...Thanks alot

v-xicai
Community Support
Community Support

Hi @var_dby ,

 

If your data changes every month, you'd better combine these month columns into a separate month column in your original data source. Then you can create measure like DAX below.

 

Measure1 = CALCULATE(SUM(Table1[sale]),FILTER(ALLSELECTED(Table1), Table1[Month] =MAX(Table1[Month])))

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

var_dby
Frequent Visitor

Hi @v-xicai ,

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors