Multirow calculation with judgements and loop nesting

[KEVINHUANG1993]

Hi all,

I got stuck in this issue. We have opeining value in Q1, so we need to get the rest of values for Q2 and Q3.

	Opening Value	Value01	Value02	Value03	Value 04	Ending Value
Q1	10	3	2	5
Q2		1	2	2
Q3		4	2	2

The calculation logic is like this:

Opening value = the ending value of previous quarter(e.g Q2(opening value)= Q1(ending value), Q3(opening value)=Q2(ending value)

Ending value = opening value + value01+value02+value03+value04

Value04 = IF((opening value+value01)>(value02+value03), min(opening value, value02+value03),0)

Following the calculation logic ,the results should be like this.

	Opening Value	Value01	Value02	Value03	Value 04	Ending Value
Q1	10	3	2	5	7	27
Q2	27	1	2	2	4	36
Q3	36	4	2	2	4	48

So I'm just wondering if there is any way to achieve this through multirow formulas in DAX. 

Hope someone could help me solve this issue. Thank you in advance for your help!

[amustafa]

I need more info...Are you getting this data in an excel or from a database? Is there a transactional table with actual date for thes values? Are you expecting 4 values for each quarter and you need to calculate the Value04 each time. providing more detail can help solve this issue better.

[KEVINHUANG1993]

We load this data from excel with actual dates for these values. I just simplify the issue to look for the solutions. We have value 01, value 02, value 03 and need to calculate value 04, opening value, and ending value for each quarter(but we have opening value for Q1). Due to the calculation dependency, the values for each quarter cannot be calculated until we calculate the values for previous quarter. Hope this information make it more clear.

[amustafa]

Hi Kevin,

Simpler way to do this calculation in your Excel workbook. Using the sample you provided, I would create a table first then use the formula like the following cell and drag it down:

Cell F2  (Value 04) =IF((B2+C2)>(D2+E2), MIN(B2,D2+E2), 0)

Cell G2 9Ending Value) =SUM(Table1[@[Opening Value]:[Value04]])

Cell G3 (from 2nd row in Opening Value) =G2

I have tried to do this Power Query and I'm not good with creating a loop function as you requested. However a more manual approach would look like this. (adjust your source file name and columnnames).

M Code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Quarter", type text}, {"Opening Value", Int64.Type}, {"Value01", Int64.Type}, {"Value02", Int64.Type}, {"Value03", Int64.Type}, {"Value04", Int64.Type}, {"Ending Value", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Opening Value", "Value01", "Value02", "Value03", "Value04", "Ending Value"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Value04", "Ending Value"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "firstrowtempvalue04", each if [Index]=0 and ([Opening Value]+[Value01])>([Value02]+[Value03]) then if [Opening Value]<[Value02]+[Value03] then [Opening Value] else [Value02]+[Value03] else 0),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"firstrowtempvalue04", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "firstrowendingvalue", each if [Index]=0 then [Opening Value]+[Value01]+[Value02]+[Value03]+[firstrowtempvalue04] else 0),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"firstrowendingvalue", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "secondrowopeningvalue", each if [Index]=1 then List.Max(#"Changed Type2"[firstrowendingvalue]) else 0),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"secondrowopeningvalue", Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type3", "secondrowtempvalue04", each if [Index]=1 and ([secondrowopeningvalue]+[Value01])>([Value02]+[Value03]) then if [secondrowopeningvalue]<[Value02]+[Value03] then [secondrowopeningvalue] else [Value02]+[Value03] else 0),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom3",{{"secondrowtempvalue04", Int64.Type}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type5", "secondrowendingvalue", each if [Index]=1 then [secondrowopeningvalue]+[Value01]+[Value02]+[Value03]+[secondrowtempvalue04] else 0),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom4",{{"secondrowendingvalue", Int64.Type}}),
#"Added Custom5" = Table.AddColumn(#"Changed Type4", "thirdrowopeningvalue", each if [Index]=2 then List.Max(#"Changed Type4"[secondrowendingvalue]) else 0),
#"Changed Type6" = Table.TransformColumnTypes(#"Added Custom5",{{"thirdrowopeningvalue", Int64.Type}}),
#"Added Custom6" = Table.AddColumn(#"Changed Type6", "thirdtempvalue04", each if [Index]=2 and ([thirdrowopeningvalue]+[Value01])>([Value02]+[Value03]) then if [thirdrowopeningvalue]<[Value02]+[Value03] then [thirdrowopeningvalue] else [Value02]+[Value03] else 0),
#"Changed Type7" = Table.TransformColumnTypes(#"Added Custom6",{{"thirdtempvalue04", Int64.Type}}),
#"Added Custom7" = Table.AddColumn(#"Changed Type7", "thirdrowendingvalue", each if [Index]= 2 then [thirdrowopeningvalue]+[Value01]+[Value02]+[Value03]+[thirdtempvalue04] else 0),
#"Changed Type8" = Table.TransformColumnTypes(#"Added Custom7",{{"thirdrowendingvalue", Int64.Type}}),
#"Added Custom8" = Table.AddColumn(#"Changed Type8", "Value04", each List.Max({[firstrowtempvalue04], [secondrowtempvalue04], [thirdtempvalue04]})),
#"Changed Type9" = Table.TransformColumnTypes(#"Added Custom8",{{"Value04", Int64.Type}}),
#"Added Custom9" = Table.AddColumn(#"Changed Type9", "Ending Value", each List.Max({[firstrowendingvalue],[secondrowendingvalue],[thirdrowendingvalue]})),
#"Changed Type10" = Table.TransformColumnTypes(#"Added Custom9",{{"Ending Value", Int64.Type}}),
#"Added Custom10" = Table.AddColumn(#"Changed Type10", "OpeningValue", each List.Max({[Opening Value],[secondrowopeningvalue],[thirdrowopeningvalue]})),
#"Changed Type11" = Table.TransformColumnTypes(#"Added Custom10",{{"OpeningValue", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type11",{"Opening Value", "firstrowtempvalue04", "firstrowendingvalue", "secondrowopeningvalue", "secondrowtempvalue04", "secondrowendingvalue", "thirdrowopeningvalue", "thirdtempvalue04", "thirdrowendingvalue"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Quarter", "Index", "OpeningValue", "Value01", "Value02", "Value03", "Value04", "Ending Value"})
in
#"Reordered Columns"

I'll kee trying for a loop function in Power Query 🙂

[amustafa]

On a second thought, this can be also done in Python if you are familier with it 🙂

import pandas as pd

# Specify the file path to your Excel file

file_path = 'Sample.xlsx'

# Read the Excel file into a DataFrame

df = pd.read_excel(file_path, engine='openpyxl')

# Calculate Value04 for the first row

if not df.empty:

    first_row = df.iloc[0]

    opening_value = first_row['OpeningValue']

    value01 = first_row['Value01']

    value02 = first_row['Value02']

    value03 = first_row['Value03']

    if (opening_value + value01) > (value02 + value03):

        df.loc[0, 'Value04'] = min(opening_value, value02 + value03)

    else:

        df.loc[0, 'Value04'] = 0

    # Calculate and fill in EndingValue for the first row

    df.loc[0, 'EndingValue'] = opening_value + value01 + value02 + value03 + df.loc[0, 'Value04']

# Fill in OpeningValue for row 2 (index 1) with the last row's EndingValue

df.loc[1, 'OpeningValue'] = df.loc[0, 'EndingValue']

# Start the loop from row 2 (Index=1)

for i in range(1, len(df)):

    # Fill in OpeningValue for the current row using the previous row's EndingValue

    df.loc[i, 'OpeningValue'] = df.loc[i - 1, 'EndingValue']

    # Calculate Value04 for the current row

    opening_value = df.loc[i, 'OpeningValue']

    value01 = df.loc[i, 'Value01']

    value02 = df.loc[i, 'Value02']

    value03 = df.loc[i, 'Value03']

    if (opening_value + value01) > (value02 + value03):

        df.loc[i, 'Value04'] = min(opening_value, value02 + value03)

    else:

        df.loc[i, 'Value04'] = 0

    # Calculate EndingValue for the current row

    df.loc[i, 'EndingValue'] = opening_value + value01 + value02 + value03 + df.loc[i, 'Value04']

# Display the updated DataFrame

print(df)

# Specify the file path for the output Excel file

output_file_path = 'Results.xlsx'

# Write the updated DataFrame to the Excel file

df.to_excel(output_file_path, index=False, engine='openpyxl')

# Confirm that the file has been written

print(f'Results have been saved to {output_file_path}')

--------RESULTS -------------

Quarter	OpeningValue	Value01	Value02	Value03	Value04	EndingValue
Q1	10	3	2	5	7	27
Q2	27	1	2	2	4	36
Q3	36	4	2	2	4	48
Q4	48	3	1	1	2	55

[KEVINHUANG1993]

Hi Amustafa, Thanks for your detailed answers. This case can be done in Excel or Python for sure, but we have to find solutions to this issue in Power BI as the rest parts of model was built in Power BI. We have thousands of rows in real model actually,and we are still struggling how to achieve this in Power BI.

[amustafa · Accepted solution]

Hi @KEVINHUANG1993 

I got the loop working in Power Query now. You can download the Sample.xlsx as the input file and see the M Code in the attached Power BI file SampleLoop.pbix in same folder. Hope this helps.

SampleLoop

Here's the full M Code. You have to enter the intial OpeningValue as a parameter and let it run.

let
Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive\SampleLoop\Sample.xlsx"), null, true),
FirstOpeningValue = 10,
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Quarter", type text}, {"Value01", Int64.Type}, {"Value02", Int64.Type}, {"Value03", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

// Custom function to perform the calculations
CustomCalculation = (Index as number, PrevEndingValue as number) as record =>
let
CurrentRow = #"Added Index"{Index},
OpeningValue = if Index = 0 then FirstOpeningValue else PrevEndingValue,
Value01 = CurrentRow[Value01],
Value02 = CurrentRow[Value02],
Value03 = CurrentRow[Value03],
Value04 = if (OpeningValue + Value01) > (Value02 + Value03) then List.Min({OpeningValue, Value02 + Value03}) else 0,
EndingValue = OpeningValue + Value01 + Value02 + Value03 + Value04
in
[Index=Index, OpeningValue=OpeningValue, Value04=Value04, EndingValue=EndingValue],

// Generate the list of calculations
Calculations = List.Generate(
()=> [Index=0, PrevEndingValue=FirstOpeningValue, Calc=CustomCalculation(0, FirstOpeningValue)],
each [Index] < Table.RowCount(#"Added Index")+1,
each [Index=[Index]+1, PrevEndingValue=[Calc][EndingValue], Calc=CustomCalculation([Index], [Calc][EndingValue])],
each [Calc]
),

// Convert the list to a table and expand the record columns
CalculatedTable = Table.FromList(Calculations, Splitter.SplitByNothing(), {"Calc"}),
ExpandedTable = Table.ExpandRecordColumn(CalculatedTable, "Calc", {"Index", "OpeningValue", "Value04", "EndingValue"}),
#"Removed Duplicates" = Table.Distinct(ExpandedTable, {"Index"}),
// Join with the original table
FinalTable = Table.NestedJoin(#"Added Index", {"Index"}, #"Removed Duplicates", {"Index"}, "NewColumns", JoinKind.LeftOuter),
#"Expanded NewColumns" = Table.ExpandTableColumn(FinalTable, "NewColumns", {"Index", "OpeningValue", "Value04", "EndingValue"}, {"NewColumns.Index", "NewColumns.OpeningValue", "NewColumns.Value04", "NewColumns.EndingValue"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumns",{"OpeningValue", "Value04", "EndingValue", "NewColumns.Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Quarter", "Index", "NewColumns.OpeningValue", "Value01", "Value02", "Value03", "NewColumns.Value04", "NewColumns.EndingValue"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"NewColumns.OpeningValue", "OpeningValue"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"OpeningValue"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"NewColumns.Value04"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"NewColumns.Value04", Int64.Type}}),
#"Replaced Value2" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"NewColumns.EndingValue"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value2",{{"NewColumns.EndingValue", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"NewColumns.Value04", "Value04"}, {"NewColumns.EndingValue", "EndingValue"}})
in
#"Renamed Columns1"

[KEVINHUANG1993]

Hi Amustafa, this solution is so cool! Thank you very much for your help! I also found another way to do it in Alteryx, as the real case is much more complicated than this one I posted.