cancel
Showing results for
Did you mean:
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),

 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov 2 3 4 5 6 7 8 9 10 11 12 2 2 2 2 2 2 2 2 2 2 2 3 4 5 6 7 8 9 10 11 12 13 4 55 5 8 7 45 344 44 4 44 33

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
Helper II

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.
6 REPLIES 6
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
Frequent Visitor

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

Helper II

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

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.

Frequent Visitor

Hi @v-xicai ,

Announcements