Disaggregation of data in Language M - Excel formulas

[Anonymous]

Hi everyone, I would much be thankful if someone can help me with this situation.

I receive weekly reports of sales by product and store, but these reports are aggregated by year (each report contains sales by year to current date) and I intend to disaggregate the data so I can have the sales of each week of the year in my reports.

Currently I am doing this with an excel formula and I want to pass this to power query / Language M

Here I paste the formula

Here is the explanation of the columns

Source.Name.1= The number of the report according to the week

SKU= Product code

Ventas YTD= Sales by year to current date

No.Tienda.number.1= Code of the store

Ventas semana= weekly sales using the excel formula

Thanks in advance for your help

[Anonymous]

Hi @Anonymous ,

I tried to create your formula on my device, but no result was returned.

Does the formula in your screenshot provide all of them?

Can you provide a detailed explanation of the logic of the formula?

Best Regards,

Stephen Tao

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

[Anonymous]

Hi Stephen

Thanks for your interest on helping me with this.

The first part of the formula is composed by “IF”. With this I am stating that if the number of the report is 1 (1 being the first report of the year), it means it can take the sales YTD as the sales of week number 1.

=IF([@[Source.Name.1]]=1;[@[Ventas YTD]];

If the number of the report is different, then the formula should subtract the current YTD sales with the result of the Filter formula (my filter formula works as the LookupValue formula).

This is the filter formula

FILTER([Ventas YTD];

([@SKU]=[SKU])*

([No.Tienda - number.1]=[@[No.Tienda - number.1]])*

([Source.Name.1]=([@[Source.Name.1]]-1));

0)

(When you see * it works like “AND”)

The filter formula filters sales per year when:

1. I am filtering the current product code ;([@SKU]=[SKU])
2. I am filtering the current store ([No.Tienda - number.1]=[@[No.Tienda - number.1]])
3. I am filtering the previous number of the report ([Source.Name.1]=([@[Source.Name.1]]-1));

If the formula cannot find any match, it puts 0.

I hope this example can be useful

Example:

Source.Name.1	SKU	Ventas YTD	No.Tienda - number.1	Ventas semana
1	AA	1	601	1
2	AA	3	601	2
3	AA	6	620	6
1	BB	2	615	2
2	BB	2	615	0

As we can see in the example, in report 1, for the AA product, in store 601, sales per year is 1, so weekly sales are also 1. In report 2, for the AA product, in store 601, sales per year are 3, so we must subtract 3-1 (1, the sales of the previous week). That's why the weekly sales in report 2 are 2. In report 3, for the AA product, there isn’t store 601, but 620. Since there are no previous reports from this store, we take that week's sales equal to sales per year, which are 6.

For the BB product, it works the same way. In report 1, the BB product, in store 615, had sales per year of 2, so sales in that week are 2. In Report 2, the BB product in store 615, reported sales per year of 2. Here we see that there was no change between report 1 and report 2 for the BB product in store 615, so when subtracting 2-2, we find that the sales of week 2 are 0.