Trusted Members
October 18, 2018
Here is a Power Query Solution
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"Bank", "Date", "Party", "DHS eqv"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Bank", type text}, {"Date", type date}, {"Party", type text}, {"DHS eqv", type number}}),
#"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted Year", "MY", each Text.Combine({[Month Name], Text.From([Year], "en-US")}, ":"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Date", "Month Name", "Year"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"MY", "Bank", "Party"}, {{"Total", each List.Sum([DHS eqv]), type nullable number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[MY]), "MY", "Total", List.Sum),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"January:2023", "February:2023", "March:2023", "April:2023", "May:2023", "June:2023"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"July:2023"}),
#"Inserted Subtraction" = Table.AddColumn(#"Replaced Value1", "Subtraction", each [#"February:2023"] - [#"January:2023"], type number),
#"Inserted Subtraction1" = Table.AddColumn(#"Inserted Subtraction", "Subtraction.1", each [#"March:2023"] - [#"February:2023"], type number),
#"Inserted Subtraction2" = Table.AddColumn(#"Inserted Subtraction1", "Subtraction.2", each [#"April:2023"] - [#"March:2023"], type number),
#"Inserted Subtraction3" = Table.AddColumn(#"Inserted Subtraction2", "Subtraction.3", each [#"May:2023"] - [#"April:2023"], type number),
#"Inserted Subtraction4" = Table.AddColumn(#"Inserted Subtraction3", "Subtraction.4", each [#"June:2023"] - [#"May:2023"], type number),
#"Inserted Subtraction5" = Table.AddColumn(#"Inserted Subtraction4", "Subtraction.5", each [#"July:2023"] - [#"June:2023"], type number),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction5",{{"Subtraction", "Diff Feb:Jan"}, {"Subtraction.1", "Diff Mar:Feb"}, {"Subtraction.2", "Diff Apr:Mar"}, {"Subtraction.3", "Diff May:Apr"}, {"Subtraction.4", "Diff Jun:May"}, {"Subtraction.5", "Diff Jul:Jun"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Bank"})
in
#"Removed Columns1"
Moderators
January 31, 2022
I used your question to learn something new myself, so thank you!
You would really need to use Power Pivot and the Data Model for this. Inspired by what I found in the link below, I added a few pivot tables to your file. See if this is what you had in mind.
https://www.daxpatterns.com/mo.....culations/
Now, DAX measures aren't very straight-forward and can become very complicated. Personally, I find them hard to write from scratch if I'm dealing with something I haven't used before. But with the help of sites like in the link above you can fairly easily find a suitable pattern and adapt it to your specific needs.
1 Guest(s)