Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
djraph
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

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_0-1669844559012.png

 

View solution in original post

10 REPLIES 10
djraph
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.

djraph_0-1669818715687.png

 

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

 

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))"

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.

 

jennratten_0-1669822462958.png

 

is this function available for PowerQuery? not Bi?

 

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.

jennratten_0-1669825692560.png

 

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

For example column runningtotal from the function vs excel formula

djraph_0-1669830858286.png

 

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_0-1669844559012.png

 

jennratten
Super User
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. 

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/ 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors