Hi @Vishy,
I have created the columns you need in Power Query. I have applied it on the S1_New table that I have obtained by exporting the data from S1 to CSV with DAX Studio.
let
Source = Csv.Document(
File.Contents(
"C:\Enterprise DNA\Forum\Dax Calculation 1st and 2nd Best Amount - Part 2 (Performance)\S1.csv"
),
[Delimiter = ";", Columns = 17, Encoding = 1252, QuoteStyle = QuoteStyle.None]
),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{
{"SUBSCRIPTION_ID", type text},
{"Total Weight", type number},
{"Date", type text},
{"2nd Best Weight", type number},
{"Current Best Weight", type number},
{"Previous index", type number},
{"Current Index", type number},
{"Avg Index", type number},
{"Output PM", type number},
{"Output Var %", type number},
{"_Previous_Best_100", type number},
{"Previous more than", type number},
{"Sum of Data_Wholesale", type number},
{"Sum of Voice_Incoming_Wholesale", type number},
{"Sum of Voice_Outgoing_Wholesale", type number},
{"Sum of Voucher_Spend", type number},
{"Sum of Data_Free_Used", type number}
}
),
#"Removed Other Columns" = Table.SelectColumns(
#"Changed Type",
{"SUBSCRIPTION_ID", "Total Weight", "Date"}
),
#"Added Custom Column" = Table.AddColumn(
#"Removed Other Columns",
"Date_New",
each Text.Combine(
{Text.Middle([Date], 8, 2), "/", Text.Middle([Date], 5, 2), "/", Text.Start([Date], 4)}
),
type text
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom Column", {"Date"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Date_New", "Date"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
#"Added Best Weight" = Table.AddColumn(
#"Changed Type1",
"Best Weight",
each List.Max(Table.SelectRows(#"Changed Type1", (a) => a[Date] <= [Date])[Total Weight]),
type number
),
#"Added Best 2nd Weight" = Table.AddColumn(
#"Added Best Weight",
"Best 2nd Weight",
each
if [Date] = List.Min(#"Added Best Weight"[Date]) then
[Best Weight]
else
List.Max(
Table.SelectRows(
#"Added Best Weight",
(a) => a[Date] <= [Date] and a[Total Weight] < [Best Weight]
)[Total Weight]
),
type number
)
in
#"Added Best 2nd Weight"
Regards
S1.csv (2.2 KB)
MVNO Test (2)_JAFP.pbix (121.2 KB)