Forum Discussion

Frequent Visitor

2 years ago

Solved

Subtracting Cumulative Data

Hi all,

I'll try and make this as clear as possible - I receive data that is already summed cumulatively each month for each contract that begins each financial year.

I need to subtract each months values from the previous months total starting fresh each financial year (month 4 does not need subtraction). Currently I am using a DAX column within Power BI and have the measure working for a single year, but once additional years' data is included it breaks the code.

The coding i currently have is as follows;

New Patients Month = if (month('New & Historic Patients'[Month Year]) = 4, [New Patients] , [New Patients] - LOOKUPVALUE([New Patients],'New & Historic Patients'[Contract Number],[Contract Number],'New & Historic Patients'[Month Year],DATEADD('New & Historic Patients'[Month Year], -1, MONTH)+0) )

Many thanks.

Hello - this is how you can do it in Power Query. This solution uses ImkeF's custom function for referencing the previous row.

let
    fn = (MyTable as table, MyColumnName as text) =>
    let
        Source = MyTable,
        ShiftedList = {null} &  List.RemoveLastN(Table.Column(Source, MyColumnName),1),
        Custom1 = Table.ToColumns(Source) & {ShiftedList},
        Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(Source) & {"Previous Row"})
    in
        Custom2 ,
    documentation = [
    Documentation.Name =  " Table.PreviousRow ",
    Documentation.Description = " Superfast way to reference previous row ",
    Documentation.LongDescription = " Superfast way to reference previous row ",
    Documentation.Category = " Table ",
    Documentation.Source = " www.TheBIccountant.com .  http://tiny.cc/hhus5y . ",
    Documentation.Version = " 1.0 ",
    Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. http://tiny.cc/hhus5y . ",
    Documentation.Examples = {[Description =  "  ",
    Code = "  ",
    Result = "  "]}],
    Result = Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)),
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdFBDoMgEIXhu7A2gXkDU3sWw/2vUSACUp/tYoKLL8gPx+HEbQ7w0LKKjx4BKJ+pTd6+QepAqpBAiHWCsG6iHnH+peKmCEgd7GWMAOtA4hDgJa82dzBL6jGVCFta0xT3kLbHeRvgKY+bjJbLfSlvsRGrP1oiEeujnCfV/zH6EFOfQ4hYY94u5w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract Number" = _t, #"Financial Year" = _t, Date = _t, #"New Patients" = _t, #"Outcome Required" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract Number", Int64.Type}, {"Financial Year", type text}, {"Date", type date}, {"New Patients", Int64.Type}, {"Outcome Required", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Contract Number", Order.Ascending}, {"Financial Year", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(
        #"Sorted Rows", 
        {"Contract Number", "Financial Year"}, 
        {
            {"Data", each fn(_, "New Patients") }
        }
    ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "New Patients", "Outcome Required", "Previous Row"}, {"Date", "New Patients", "Outcome Required", "Previous Row"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "New", each [New Patients] - ( [Previous Row] ?? 0 ))
in
    #"Added Custom"

slorin

2 years ago

Hi,

Another solution with Table.NestedJoin

let
Source = YourSource,
Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
Next_Month = Table.TransformColumns(Index,{{"Date", each Date.AddMonths(_,1), type date}}),
NestedJoin = Table.NestedJoin(
      Index, {"Contract Number", "Financial Year", "Date"},
      Next_Month, {"Contract Number", "Financial Year", "Date"}, "Prev_Month", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(NestedJoin, "Prev_Month", {"New Patients"}, {"New Patients.1"}),
Subtracting = Table.AddColumn(Expand, "Outcome Required", each List.Sum({[New Patients], -[New Patients.1]}), type number),
Sort = Table.Sort(Subtracting,{{"Index", Order.Ascending}}),
SelectColumns = Table.SelectColumns(Sort,{"Contract Number", "Financial Year", "Date", "New Patients", "Outcome Required"})
in
SelectColumns

Stéphane

6 Replies

slorin

Super User

2 years ago

Hi,

Another solution with Table.NestedJoin

let
Source = YourSource,
Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
Next_Month = Table.TransformColumns(Index,{{"Date", each Date.AddMonths(_,1), type date}}),
NestedJoin = Table.NestedJoin(
      Index, {"Contract Number", "Financial Year", "Date"},
      Next_Month, {"Contract Number", "Financial Year", "Date"}, "Prev_Month", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(NestedJoin, "Prev_Month", {"New Patients"}, {"New Patients.1"}),
Subtracting = Table.AddColumn(Expand, "Outcome Required", each List.Sum({[New Patients], -[New Patients.1]}), type number),
Sort = Table.Sort(Subtracting,{{"Index", Order.Ascending}}),
SelectColumns = Table.SelectColumns(Sort,{"Contract Number", "Financial Year", "Date", "New Patients", "Outcome Required"})
in
SelectColumns

Stéphane

BaxterT
Frequent Visitor
2 years ago
Many thanks for this Stephane!
What changes would I need to make if I had more columns also with cumulative data. Everything else remains the same - dates, contracts, financial year. I have about 7 columns with this issue. Is it possible to combine all within this same process?

Kind regards.

slorin
Super User
2 years ago
You can unpivot your 7 columns before join
lbendlin
Super User
2 years ago
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

BaxterT

Frequent Visitor

2 years ago

Many thanks for your reply - I am happy to have the solution in DAX or M Code, either works for me. I hope that this format is sufficient.

Contract Number	Financial Year	Date	New Patients	Outcome Required
1	22/23	01/04/22	5	5
1	22/23	01/05/22	15	10
1	22/23	01/06/22	20	5
2	22/23	01/04/22	7	7
2	22/23	01/05/22	10	3
2	22/23	01/06/22	15	5
3	22/23	01/04/22	6	6
3	22/23	01/05/22	10	4
3	22/23	01/06/22	20	10
1	23/24	01/04/23	2	2
1	23/24	01/05/23	8	6
1	23/24	01/06/23	14	6
2	23/24	01/04/23	10	10
2	23/24	01/05/23	15	5
2	23/24	01/06/23	20	5
3	23/24	01/04/23	10	10
3	23/24	01/05/23	11	1
3	23/24	01/06/23	20	9

jennratten

Super User

2 years ago

Hello - this is how you can do it in Power Query. This solution uses ImkeF's custom function for referencing the previous row.

let
    fn = (MyTable as table, MyColumnName as text) =>
    let
        Source = MyTable,
        ShiftedList = {null} &  List.RemoveLastN(Table.Column(Source, MyColumnName),1),
        Custom1 = Table.ToColumns(Source) & {ShiftedList},
        Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(Source) & {"Previous Row"})
    in
        Custom2 ,
    documentation = [
    Documentation.Name =  " Table.PreviousRow ",
    Documentation.Description = " Superfast way to reference previous row ",
    Documentation.LongDescription = " Superfast way to reference previous row ",
    Documentation.Category = " Table ",
    Documentation.Source = " www.TheBIccountant.com .  http://tiny.cc/hhus5y . ",
    Documentation.Version = " 1.0 ",
    Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. http://tiny.cc/hhus5y . ",
    Documentation.Examples = {[Description =  "  ",
    Code = "  ",
    Result = "  "]}],
    Result = Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)),
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdFBDoMgEIXhu7A2gXkDU3sWw/2vUSACUp/tYoKLL8gPx+HEbQ7w0LKKjx4BKJ+pTd6+QepAqpBAiHWCsG6iHnH+peKmCEgd7GWMAOtA4hDgJa82dzBL6jGVCFta0xT3kLbHeRvgKY+bjJbLfSlvsRGrP1oiEeujnCfV/zH6EFOfQ4hYY94u5w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract Number" = _t, #"Financial Year" = _t, Date = _t, #"New Patients" = _t, #"Outcome Required" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract Number", Int64.Type}, {"Financial Year", type text}, {"Date", type date}, {"New Patients", Int64.Type}, {"Outcome Required", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Contract Number", Order.Ascending}, {"Financial Year", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(
        #"Sorted Rows", 
        {"Contract Number", "Financial Year"}, 
        {
            {"Data", each fn(_, "New Patients") }
        }
    ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "New Patients", "Outcome Required", "Previous Row"}, {"Date", "New Patients", "Outcome Required", "Previous Row"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "New", each [New Patients] - ( [Previous Row] ?? 0 ))
in
    #"Added Custom"

Forum Discussion

Subtracting Cumulative Data

6 Replies

Related content

Cumulative Subtraction using Criteria

Subtraction calulation

Cumulative Total

staffing levels cumulative data

filter? Cumulative subtraction doesn't work