求一函数正确写法

zx168

各位老师朋友新年好！由于个所税从今年1月实行累计预扣法，所以除了今年1月份外，其他月份都要计算出累计应税所得、累计个所税。当月个所税=累计个所税-上月累计个所税。本人仅用两个月数据，1月份个税按正常的函数计算，从2月份开始，我想用PQ办法进行汇总，求出截止2月份累计应税所得、累计个所税。可是，当我把累计个所税计算函数输入增加的自定义列后，计算显示错误。我不知道这个函数正确的写法是什么，求解。谢谢!
标红的为自定义列和函数。
let
    源 = Excel.Workbook(File.Contents("C:\Users\Administrator\Desktop\2019年工资表.xlsx"), null, true),
    筛选的行 = Table.SelectRows(源, each ([Name] = "1月" or [Name] = "2月")),
    删除的其他列 = Table.SelectColumns(筛选的行,{"Name", "Data"}),
    #"展开的“Data”" = Table.ExpandTableColumn(删除的其他列, "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    提升的标题 = Table.PromoteHeaders(#"展开的“Data”", [PromoteAllScalars=true]),
    更改的类型 = Table.TransformColumnTypes(提升的标题,{{"1月", type date}, {"姓名", type text}, {"应发工资", type any}, {"扣社保", type any}, {"免征额", type any}, {"专扣", type any}, {"本月个税", type any}, {"实发工资", type any}, {"Column9", type any}, {"Column10", type any}}),
    删除的列 = Table.RemoveColumns(更改的类型,{"1月", "Column9", "Column10"}),
    筛选的行1 = Table.SelectRows(删除的列, each ([姓名] <> "姓名" and [姓名] <> "汇总")),
    更改的类型1 = Table.TransformColumnTypes(筛选的行1,{{"应发工资", type number}, {"扣社保", type number}, {"免征额", type number}, {"专扣", type number}, {"本月个税", type number}, {"实发工资", type number}}),
    删除的列1 = Table.RemoveColumns(更改的类型1,{"实发工资"}),
    替换的值 = Table.ReplaceValue(删除的列1,null,0,Replacer.ReplaceValue,{"应发工资", "扣社保", "免征额", "专扣", "本月个税"}),
    分组的行 = Table.Group(替换的值, {"姓名"}, {{"累计应发工资", each List.Sum([应发工资]), type number}, {"累计扣社保", each List.Sum([扣社保]), type number}, {"累计免征额", each List.Sum([免征额]), type number}, {"累计专扣", each List.Sum([专扣]), type number}, {"累计上月个税", each List.Sum([本月个税]), type number}}),
    已添加自定义 = Table.AddColumn(分组的行, "累计应税所得", each [累计应发工资]-[累计扣社保]-[累计免征额]-[累计专扣]),
    更改的类型2 = Table.TransformColumnTypes(已添加自定义,{{"累计应税所得", type number}}),
    已添加自定义1 = Table.AddColumn(更改的类型2, "累计个所税", each ROUND(MAX(([累计应税所得])*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,210,1410,2660,4410,7160,15160}*12,0),2))
in
    已添加自定义1

wdx223

1. = Table.AddColumn(更改的类型2, "累计个所税", each Number.Round(List.Max(List.Transform({0..6},(x)=>[累计应税所得]*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}{x}-{0,210,1410,2660,4410,7160,15160}{x}*12)&{0}),2))

复制代码

zx168

wdx223 发表于 2019-2-11 09:51

朋友，新年好，谢谢你！