Calculating delta between cumulative revenue values for the closest time periods for each project

[Forgottnak]

Dear PowerBI users,

I would really appreciate your help to resolve the following issue. I found solutions for similar ones but not EXACTLY for this one. My goal is to resolve this as efficiently as possible and I understood that it should be the PowerQuery way. If a more efficient solution is to use calculated column in PowerBI, I am also OK to get such a solution.

I have a data export from SAP as xls which looks like the table below. Revenue values are always a cumulative value for the given project from its start up to the given time period. Data come out not sorted. There may be decreases of values between time periods due to credit-note being issued. Date column is not included.

I need to import this data source to PowerBI data model so that besides all of the columns with original values a column is added showing the revenue delta value for the given project between closest time periods. 

This is a simplified example. In reality there is more financial values (orders received/revenue/production costs/product quantity/etc. up to approx. ten different ones) and thousands of projects (data source has approx. 80000 lines) for which I will need to perform this calculation and at columns with deltas. Therefore I believe that the efficiency of the solution is important. 

Resolving this with your kind help would push me to the sky 🙂 Thanks,

Jan

Project	Revenue	Month	Year
H1	0	2	2023
H1	100	3	2023
H2	0	12	2022
H2	200	4	2023
H1	400	7	2023
H1	380	8	2023
H2	170	2	2023
H2	250	2	2024

[AlienSx]

hello, @Forgottnak if I had sample with 2 or more columns to calculate then it would be a little bit more complicated. But [Revenue] is the only column in your sample so this is just revenue delta. 

let
    Source = your_table,
    f = (tbl) =>
        [a = List.Buffer(Table.ToRecords(tbl)),
        g = List.Generate(
            () => [i = 0, r = a{0} & [delta = a{0}[Revenue]]],
            (x) => x[i] < List.Count(a),
            (x) => [i = x[i] + 1, r = a{i} & [delta = a{i}[Revenue] - x[r][Revenue]]],
            (x) => x[r]
        ),
        z = Table.FromRecords(g)][z],
    gr = Table.Group(Source, "Project", {{"rows", (x) => f(Table.Sort(x, {"Year", "Month"}))}}),
    ex = Table.ExpandTableColumn(gr, "rows", {"Revenue", "Month", "Year", "delta"})
in
    ex

[Forgottnak]

Hi @AlienSx 

unfortunately it returns a cyclical reference 

The whole code entered is this:

let
    Zdroj = Excel.Workbook(File.Contents("\\europe.abb.com\cz\BRQ1\PTMV\01_divize_swgr\01_Oddeleni\02_Kontroling\2024\Analýzy\PBITEST.xlsx"), null, true),
    Table1_Table = Zdroj{[Item="Table1",Kind="Table"]}[Data],
    #"Změněný typ" = Table.TransformColumnTypes(Table1_Table,{{"Project", type text}, {"Revenue", Int64.Type}, {"Month", Int64.Type}, {"Year", Int64.Type}})
,

    
    f = (tbl) =>
        [a = List.Buffer(Table.ToRecords(tbl)),
        g = List.Generate(
            () => [i = 0, r = a{0} & [delta = a{0}[Revenue]]],
            (x) => x[i] < List.Count(a),
            (x) => [i = x[i] + 1, r = a{i} & [delta = a{i}[Revenue] - x[r][Revenue]]],
            (x) => x[r]
        ),
        z = Table.FromRecords(g)][z],
    gr = Table.Group(Table1,"Project", {{"rows", (x) => f(Table.Sort(x, {"Year", "Month"}))}}),
    ex = Table.ExpandTableColumn(gr, "rows", {"Revenue", "Month", "Year", "delta"})
in
    ex

I was not able to resolve this till now 😕 If you know the way, I would appreciate it. Just thinking - as mentioned earlier, the real situation will include more than one values like Revenues, f.e. Production costs. If you can post also the solution for at least two such values, it would be great. Thank you, regards,

Jan

[AlienSx · Accepted solution]

@Forgottnak watch this vid about how to integrate this code into your solution

let
    data = your_table,
    cols = {"Revenue", "Production costs"},
    n = List.Count(cols),
    delta_cols = List.Buffer(List.Transform(cols, (x) => "delta " & x)),
    order = Table.ReorderColumns(data, List.RemoveItems(Table.ColumnNames(data), cols) & cols),
    delta = (lst_01, lst_02) =>
        [zip = List.Zip({lst_01, lst_02}),
        dlt = List.Transform(zip, (x) => x{1} - x{0})][dlt],
    f = (tbl) => 
        [a = List.Buffer(Table.ToList(tbl, (x) => x)),
        g = List.Generate(
            () => [i = 0, r = a{0} & List.LastN(a{0}, n)],
            (x) => x[i] < List.Count(a),
            (x) => [i = x[i] + 1, r = a{i} & delta(List.LastN(a{i - 1}, n), List.LastN(a{i}, n))],
            (x) => x[r]
        )][g],
    gr = Table.Group(order, "Project", {"all", (x) => f(Table.Sort(x, {"Year", "Month"}))}),
    z = Table.FromList(List.Combine(gr[all]), (x) => x, Table.ColumnNames(order) & delta_cols)
in
    z

[Forgottnak]

Dear @AlienSx 

that works perfectly 🙂 Thanks for directing me also to the helpful video on how to incorporate the code into an existing solution. Big thanks overall,

Jan

[Greg_Deckler]

@Forgottnak Not PowerQuery but pretty classic DAX MTBF pattern. PBIX is below sig.

Column = 
    VAR __Project = [Project]
    VAR __Year = [Year]
    VAR __Month = [Month]
    VAR __Revenue = [Revenue]
    VAR __PreviousMonth = 
        MAXX(
            FILTER(
                'Table',
                [Project] = __Project && [Year] = __Year && [Month] < __Month
            ),
            [Month]
        )
    VAR __PreviousValue = 
        MAXX(
            FILTER(
                'Table',
                [Project] = __Project && [Year] = __Year && [Month] < __PreviousMonth
            ),
            [Revenue]
        )
    VAR __Result = __Revenue - __PreviousValue
RETURN
    __Result

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
 ( __Current - __Previous ) * 1.

[Forgottnak]

Dear @Greg_Deckler ,

perfect, thank you! 🙂 It was not working properly (maybe my mistake) but together with my son we made it finally, using your script as a basis. Finally I added Date column created from Month and Year columns and used it to define "PreviousDate". Doing it this way all the deltas are calculated properly. To note - some of the columns are named differently compared to the example however the substance is the same. I will try this for multiple columns and see whether the the solution still works fast enough. Thanks a lot 🙂

Jan

OR1_PER = VAR __Project = [Definice projektu]
    VAR __Date = [Date]
    VAR __OR1PER = [Value]
    VAR __PreviousDate =
        MAXX(
            FILTER(
                CO_ORREV3,
                [Definice projektu] = __Project && [Date] < __Date
            ),
            [Date]
        )
    VAR __PreviousValue =
        MAXX(
            FILTER(
                CO_ORREV3,
                [Definice projektu] = __Project && [Date] = __PreviousDate
            ),
            [Value]
        )
    VAR __Result = __OR1PER - __PreviousValue
RETURN
    __Result