Calculate Total weekly from daily figures

[alexcatala]

Hi,

I am trying to obtain the weekly QTY based on the daily QTY.

I would like to be able to extract the weekly QTY by store.

As you can see I have the daily QTY, store and the week number. So I would like to add an extra column or measure to know the total QTY per week and per store.

Any suggestion?

Thanks for your time

[dkaushik]

Hi @alexcatala ,

Try this, 

Weekly Qty = sumx(filter(Sheet1, [store] =earlier(Sheet1[Store]) && [week] =earlier([week])),[Qty])

Regards,

Dheeraj

Please mark as Solution if it works 🙂

[alexcatala]

Hi @dkaushik 

It is a closer answer but the problem is the year, it is counting the total from all years, instead of per year.

Any idea?

[amitchandak]

@alexcatala ,In a table/matrix or any visual. Take un summarized week column and sum(table[Qty]), you will week wise data. 

new measure = sum(table[Qty])

Or Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Weekly Qty as column = sumx(filter(Table, [store] =earlier([store]) && [week] <earlier([week])),[Qty])

But measure in visual is a better idea

[alexcatala]

Hi @amitchandak 

After adding your suggestion this is what appeared.

It doesn't match the total number it should. It is inaccurate. For the 1st example if avg daily is 300 it should be around 2.100, but no 109.074.

Any suggestion?

[dkaushik · Accepted solution]

Hi @alexcatala ,

In that case, please use following formula:

Weekly Qty = CALCULATE( SUM(Sheet1[Qty]), ALLEXCEPT(Sheet1, Sheet1[Year], Sheet1[Week], Sheet1[Store]))

Thanks,

Dheeraj