Power Query running total of the last 30 rows

[djraph]

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.

[djraph]

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.

[jennratten]

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

[djraph]

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...

"List.Sum(List.LastN(#"Added Index"[Sum Remuneration],30))"

[jennratten]

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.

[djraph]

is this function available for PowerQuery? not Bi?

[jennratten]

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.

[djraph]

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...

[djraph]

For example column runningtotal from the function vs excel formula

[jennratten · Accepted solution]

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
ADDCOLUMNS (
    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.

[jennratten]

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. 

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523 

How to Get Your Questions Answered Quickly

https://www.thebiccountant.com/2018/09/30/memory-efficient-clustered-running-total-in-power-bi/