How to create a custom 12 months sum column in power Query Editor

[PBIAnonymousPBI]

Hello All,

I have a file that i pull from the system for which i have the monthly consumption per month for each products in each column. Which means that everytime i receive a new extract a new column is added with the consumption of the month. 

I would like to make a custom column/calculation that would allow me to have the sum of the value for the past 12M and past 18M. 

I have heard that the indexing of the columns would be a good way to proceed but then i am stuck. 

Thank you for your help.

Kind regards

Tom Collin

[daXtreme]

You should not store such data in a wide-format table but in a narrow-format table. So, each new entry should not create a new column because this is not how you handle such things in PBI, but it should add new rows. To make sure this is the case, use Power Query to mash up your data into the right format.

Your table should look something like:

ProdID | Month | Consumption

Not like this:

Consumption | Month | Prod1 | Prod2 | Prod3 |...

The above format is WRONG. Simple as that.

Once you've got the right table, it's easy to write the DAX you want. By the way, Month and Product should be extracted into their own dimensions and joined to the fact table that stores consumption. If you don't know what I'm talking about... please read this: Understand star schema and the importance for Power BI - Power BI | Microsoft Learn