Блог

Я пытаюсь рассчитать разницу между совокупными расходами между двумя строками.

 Net expenditure for the month = 

var diff= 
'Source data'[Cummulative expense for the month]- 
CALCULATE(
    SUM('Source data'[Cummulative expense for the month]),
    FILTER(
        'Source data',
        'Source data'[index]=EARLIER('Source data'[Numerator])
    )
) 

return 
IF(
    diff=VALUE('Source data'[Cummulative expense for the month]),
    0,
    diff
)

 

Не знаю, в чем тут ошибка. Я также прикрепляю скриншот данных

Вы можете достичь этого с помощью power query

 let
    Source = Excel.Workbook(File.Contents("C:Usersanumua2Downloadsim_08nov.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Cumulative Expense for month", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Prev" = Table.AddColumn(#"Added Index", "Custom", each #"Added Index"{[Index]-2}[#"Cumulative Expense for month"]),
    #"Changed Type1" = Table.TransformColumnTypes(Prev,{{"Custom", type number}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Custom", 0}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Errors", "Subtraction", each [Cumulative Expense for month] - [Custom], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction",{{"Custom", "Prev row"}, {"Subtraction", "Net Expenditure for month"}})
in
    #"Renamed Columns"
 

Не могли бы вы, пожалуйста, попробовать что-нибудь, как показано ниже-

 Net expenditure for the month = 

var current_row_index = min('Source data'[index])
var current_row_cummulative_expense = min('Source data'[Cummulative expense for the month])

var diff= 
current_row_cummulative_expense - 
CALCULATE(
    SUM('Source data'[Cummulative expense for the month]),
    FILTER(
        ALL('Source data'),
        'Source data'[index] = current_row_index - 1
    )
) 

return 
IF(
    diff=VALUE(min('Source data'[Cummulative expense for the month])),
    0,
    diff
)