cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Power Query running total of the last 30 rows

Hello,

I've tried and looked here to help me on this but i didn't find dit.

I need to do a sum on the last 30 rows, tried with the cumulative but it's all the row in this case.
Thanks for you help,

Raph.

1 ACCEPTED SOLUTION
Super User

It looks like you are actually wanting the following:

• Is the row in the last 30 rows of the table?
• If no, return null.
• If yes, return the cumulative sum for the last 30 days.

This result would best be handled with a DAX measure instead of Power Query.  To use this solution, you will need a date table that is related to your data/fact table.  You will also need two measures.  The scripts for these are listed below and I have also included an updated sample file.

Date Table:

``````Date =
VAR MinYear = YEAR ( MIN ( Sales[OrderDate] ) )
VAR MaxYear = YEAR ( MAX ( Sales[OrderDate] ) )
RETURN
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) <= MaxYear, YEAR ( [Date] ) >= MinYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)``````

Measure #1: \$ Sales

``\$ Sales = sum ( Sales[Sales Amount] )``

Measure #2: Sales with a rolling total for the last 30 days

``````\$ Sales RT 30 Days =
VAR LastVisibleDate =
MAX ( 'Date'[Date] )
VAR LastVisibleDateMinus30 =
LastVisibleDate - 30
VAR FirstVisibleDate =
MIN ( 'Date'[Date] )
VAR CurrentDate =
selectedvalue ( 'Date'[Date] )
VAR LastDateWithSales =
CALCULATE (
MAX ( 'Sales'[OrderDate] ),
REMOVEFILTERS ()
)
VAR LastDateWithSalesMinus30 =
LastDateWithSales - 30
VAR Result =
IF (
and (
FirstVisibleDate <= LastDateWithSales,
CurrentDate > LastDateWithSalesMinus30
),
CALCULATE (
[\$ Sales],
DATESBETWEEN (
'Date'[Date],
LastVisibleDateMinus30,
LastVisibleDate
),
'Date'[Date] <= LastVisibleDate
),
blank()
)
RETURN
Result``````

Result:

When I filter for the last 30 days (from the last date with sales), I can see that the cumulative sum for the latest date matches the total sum for the base column.

10 REPLIES 10
New Member

Hello, thank for you message, i didn't find help for my case in this one.

Here you an example of what i do in a excel sheet.

In the 6th column from the left i made a calculation based on the last 30 rows of col 3 and 4.  I would like to replicate this in Power Query in Excel... thanks.

Super User

One option for having the running total only for the bottom 30 rows and have any rows above it be null would be to add two steps: one for a table with only the bottom 30 rows and another for a table with the rest of the rows.  You could then apply the running total to the table containing only the bottom30 rows, using Imke's examples, and then append the two tables to get your result. I have included a sample file that demonstrates this.  https://www.thebiccountant.com/2018/09/30/memory-efficient-clustered-running-total-in-power-bi/

``````    varAllButLast30Rows = Table.RemoveLastN ( #"Changed Type", 30 ),
varLast30Rows = Table.LastN ( #"Changed Type", 30 ),
// Add the running total using the table named varLast30Rows
RunningTotal = fnRunningTotal(varLast30Rows, "OrderDateKey", "Sales Amount"),
// Append the tables to get the result
Result = Table.Combine ( { varAllButLast30Rows, RunningTotal } )
in
Result
``````

New Member

Hello, my bad because, I need those rows as i need a rolling running total...

this formula isn't efficient for example, it give me the same total for all the rows...

Super User

Did you review the sample pbix that I attached?  The running total in it does not have the same values on each row (snip below).  You will need to use the Imke's function, which is included in the sample file.

New Member

is this function available for PowerQuery? not Bi?

Super User

Yes, the function included in the sample file is a Power Query function.  If you open the sample file and then go to the Power Query editor, you can select the custom function by clicking on it in the queries pane and then Ctrl+C to copy it and Ctrl+V to paste it into your pbix.

New Member

I try it, but it give me the result for the last 30 days, but not rolling as i need to have the total rolling from May to june...

New Member

For example column runningtotal from the function vs excel formula

Super User

It looks like you are actually wanting the following:

• Is the row in the last 30 rows of the table?
• If no, return null.
• If yes, return the cumulative sum for the last 30 days.

This result would best be handled with a DAX measure instead of Power Query.  To use this solution, you will need a date table that is related to your data/fact table.  You will also need two measures.  The scripts for these are listed below and I have also included an updated sample file.

Date Table:

``````Date =
VAR MinYear = YEAR ( MIN ( Sales[OrderDate] ) )
VAR MaxYear = YEAR ( MAX ( Sales[OrderDate] ) )
RETURN
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) <= MaxYear, YEAR ( [Date] ) >= MinYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)``````

Measure #1: \$ Sales

``\$ Sales = sum ( Sales[Sales Amount] )``

Measure #2: Sales with a rolling total for the last 30 days

``````\$ Sales RT 30 Days =
VAR LastVisibleDate =
MAX ( 'Date'[Date] )
VAR LastVisibleDateMinus30 =
LastVisibleDate - 30
VAR FirstVisibleDate =
MIN ( 'Date'[Date] )
VAR CurrentDate =
selectedvalue ( 'Date'[Date] )
VAR LastDateWithSales =
CALCULATE (
MAX ( 'Sales'[OrderDate] ),
REMOVEFILTERS ()
)
VAR LastDateWithSalesMinus30 =
LastDateWithSales - 30
VAR Result =
IF (
and (
FirstVisibleDate <= LastDateWithSales,
CurrentDate > LastDateWithSalesMinus30
),
CALCULATE (
[\$ Sales],
DATESBETWEEN (
'Date'[Date],
LastVisibleDateMinus30,
LastVisibleDate
),
'Date'[Date] <= LastVisibleDate
),
blank()
)
RETURN
Result``````

Result:

When I filter for the last 30 days (from the last date with sales), I can see that the cumulative sum for the latest date matches the total sum for the base column.

Super User

Hello - the link below provides a great example of how to calculate a running total.  If this does not meet your needs please provide sample data, screenshots, explanations of the expected result, etc.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors