How to define dynamically sum of last five columns

[Rahul_SC]

Hi All,

I need to create a table in power bi where need to show data of last five weeks on weekly basis. Just like, I did in the picture below. This I have prepared these columns in power query then loaded to desktop. Now, everyweek I need to revmove the most previous week (i.e-week 18) then add new week (i.e-week 23) and need to show sum in canceled sailing column from week 19-23 same in scheduled sailing col. For this, everyweek when next week data is updated in the source file then I have to open power query then do calculation as per the recent week (19-23) then in power bi desktop also remove col of week 18 and add new week col (week 23) to show the current data. This takes a lot of time each week.

I want to make it dynamic, means I just need to refresh then new week data is added in the table. I do not need to open power query each time. 

I thought of a solution like creating a saparate data col for week and data for cancel sailing would use it. 

I need to know a function which is used for adding based on last five columns. or any other way of getting this done.

Regards,

[KT_Bsmart2gethe · Accepted solution]

Hi @Rahul_SC ,

What you can do in this case is to add the remove column step.

To turn the step into dynamically pick up the last six weeks:

Add a custom step:

Table.RemoveColumns(Previous step, List.RemoveLastN(List.Sort(List.Select(Table.ColumnNames(Previous step), each Text.Contains(_,"Week ")), Order.Ascending),6))

Let me know if this helps you to resolve your challenge.

Regards

KT

[Vijay_A_Verma · Accepted solution]

For dynamically adding last 5 columns, use below formula (you can keep adding columns and formula is not required to be changed)

= List.Sum(List.LastN(Record.ToList(_),5))

[KT_Bsmart2gethe · Accepted solution]

Hi @Rahul_SC ,

What you can do in this case is to add the remove column step.

To turn the step into dynamically pick up the last six weeks:

Add a custom step:

Table.RemoveColumns(Previous step, List.RemoveLastN(List.Sort(List.Select(Table.ColumnNames(Previous step), each Text.Contains(_,"Week ")), Order.Ascending),6))

Let me know if this helps you to resolve your challenge.

Regards

KT

[Rahul_SC]

@Vijay_A_Verma , thanks for responding.

@KT_Bsmart2gethe a big thanks to Kirwin. You helped me a lot from the beginning till my end outcome what I excatly wanted.