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

[Anonymous]

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])

[Anonymous]

Hi @Anonymous 

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

[Anonymous]

Hi @Anonymous 

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

[Anonymous · Accepted solution]

Hi @Anonymous 

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.

[Anonymous]

Hi @Anonymous 

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

[v-xicai]

Hi @Anonymous ,

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.

[Anonymous]

Hi @v-xicai ,

Actually I want to add whole months from January till current month means all month we need to take for calculate sales meanwhile when month changes it should identify the current month and take calculation for that month also.... Hope you understand my query,, Thanks