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

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

Reply
KennethMau
Regular Visitor

Sumifs in power query

Hi all!


I'm working in the automation of a report. Everything has gone well, but I haven't found a work around for the next problem. 

The dataset I'm working with looks like this (with other columns, these not being used in the calculation). There are multiple ID's and the date range goes to 2 years in the future.

 

 

iDweek star dateForecast value
A12/4/231500
B12/4/231600
C12/4/232000
A12/11/232000

 

I have loaded this table in a sheet and created a new column named "12 Weeks Forecast" which is just the sum of the ID values for the next 12 weeks using the following formula : 

 

=SUMIFS([Total Demand],[ID],[@ID],[Week_Start_Date],">"&[@[Week_Start_Date]],[Week_Start_Date],"<="&[@[Week_Start_Date]]+84)


so the criteria would be:

  • ID are the same that the current row
  • week start date is greater than the actual row week start date, but it must be equals or less than the actual row week start date plus 84 days (so the next 12 weeks are considered)

 

the problem is that this causes the report to be very slow, so I'm hoping in finding a way to calculate this column directly in power query, but have not been successful yet.

 

Is There a way I'm able to perform this in power query?

and if's not, it's possible in power pivot? (Just if's not possible in query)

 

Thanks for any info

2 ACCEPTED SOLUTIONS
spinfuzer
Super User
Super User

Replaced with slightly faster version.

 

 

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcyxDcAgDAXRXVwj2XyMUycZw2L/NQiJpQDd6RXnTiclymBllFFVhFpyula24HthSHBMct58XMAwho6q0+XnMl8ePj7WbRJcZZ0E28utAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"week start date" = _t, #"Forecast value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"week start date", type date}, {"Forecast value", Int64.Type}}),
    sumifs =
        (tbl as table) =>

            Table.AddColumn(
                tbl, 
                "12 week forecast", 
                each List.Sum(Table.SelectRows(
                    tbl,
                    (r) => r[week start date] > [week start date]
                    and r[week start date] <= Date.AddDays([week start date],84)
                )[Forecast value]
                )
            )
        ,
    group1 = Table.Group(#"Changed Type", {"ID", "week start date"}, {"Forecast value", each List.Sum([Forecast value])}),
    // first group by ID and dates then by just id.  you could skip this grouping if there you do not have multiple rows with the same ID and date.
    group2 = 
        let
            buffer = Table.Buffer(group1)
        in
            Table.Group( buffer, "ID", {"sumifs", each sumifs(_)}),
    #"Removed Other Columns" = Table.SelectColumns(group2,{"sumifs"}),
    #"Expanded sumifs" = Table.ExpandTableColumn(#"Removed Other Columns", "sumifs", {"ID", "week start date", "Forecast value", "12 week forecast"}, {"ID", "week start date", "Forecast value", "12 week forecast"})
in
    #"Expanded sumifs"

 

 

 

 

 

 

View solution in original post

AlienSx
Super User
Super User

hi, @KennethMau a little bit complicated but I tried to speed things up. Replace your_table with your table or last step name. 

    // this function calculates adds "weeks fc" column as running total for upcoming number of weeks
    wks_forecast = (tbl, weeks) =>
        [rows = List.Buffer(Table.ToRecords(tbl)),
        values = List.Buffer(tbl[Forecast value]),
        gena = List.Generate(
            () => 
                [i = 0, 
                fc = try List.Sum(List.Range(values, 1, weeks)) otherwise 0, 
                rec = rows{i} & [weeks fc = fc]],
            (x) => rows{x[i]}? <> null,
            (x) => 
                [i = x[i] + 1, 
                fc = x[fc] - values{i} + (values{i + weeks}? ?? 0), 
                rec = rows{i} & [weeks fc = fc]],
            (x) => x[rec]
        ),
        t = Table.FromRecords(gena)][t],
    // here we group by ID ans run our function
    g = Table.Group(your_table, "ID", {"fcast", each wks_forecast(Table.Sort(_, "week start date"), 12)}),
    // combine things together
    z = Table.Combine(g[fcast])

 

View solution in original post

2 REPLIES 2
AlienSx
Super User
Super User

hi, @KennethMau a little bit complicated but I tried to speed things up. Replace your_table with your table or last step name. 

    // this function calculates adds "weeks fc" column as running total for upcoming number of weeks
    wks_forecast = (tbl, weeks) =>
        [rows = List.Buffer(Table.ToRecords(tbl)),
        values = List.Buffer(tbl[Forecast value]),
        gena = List.Generate(
            () => 
                [i = 0, 
                fc = try List.Sum(List.Range(values, 1, weeks)) otherwise 0, 
                rec = rows{i} & [weeks fc = fc]],
            (x) => rows{x[i]}? <> null,
            (x) => 
                [i = x[i] + 1, 
                fc = x[fc] - values{i} + (values{i + weeks}? ?? 0), 
                rec = rows{i} & [weeks fc = fc]],
            (x) => x[rec]
        ),
        t = Table.FromRecords(gena)][t],
    // here we group by ID ans run our function
    g = Table.Group(your_table, "ID", {"fcast", each wks_forecast(Table.Sort(_, "week start date"), 12)}),
    // combine things together
    z = Table.Combine(g[fcast])

 

spinfuzer
Super User
Super User

Replaced with slightly faster version.

 

 

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcyxDcAgDAXRXVwj2XyMUycZw2L/NQiJpQDd6RXnTiclymBllFFVhFpyula24HthSHBMct58XMAwho6q0+XnMl8ePj7WbRJcZZ0E28utAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"week start date" = _t, #"Forecast value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"week start date", type date}, {"Forecast value", Int64.Type}}),
    sumifs =
        (tbl as table) =>

            Table.AddColumn(
                tbl, 
                "12 week forecast", 
                each List.Sum(Table.SelectRows(
                    tbl,
                    (r) => r[week start date] > [week start date]
                    and r[week start date] <= Date.AddDays([week start date],84)
                )[Forecast value]
                )
            )
        ,
    group1 = Table.Group(#"Changed Type", {"ID", "week start date"}, {"Forecast value", each List.Sum([Forecast value])}),
    // first group by ID and dates then by just id.  you could skip this grouping if there you do not have multiple rows with the same ID and date.
    group2 = 
        let
            buffer = Table.Buffer(group1)
        in
            Table.Group( buffer, "ID", {"sumifs", each sumifs(_)}),
    #"Removed Other Columns" = Table.SelectColumns(group2,{"sumifs"}),
    #"Expanded sumifs" = Table.ExpandTableColumn(#"Removed Other Columns", "sumifs", {"ID", "week start date", "Forecast value", "12 week forecast"}, {"ID", "week start date", "Forecast value", "12 week forecast"})
in
    #"Expanded sumifs"

 

 

 

 

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors