Forum Discussion

kangx322's avatar
kangx322
Frequent Visitor
4 years ago
Solved

Calculation with previous month's value

I have excel calculation that I would like to replicate in Power Query.    Is this possible in Power Query?      
Need Help
Tips and Tricks
  • smpa01's avatar
    smpa01
    4 years ago

    kangx322  try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACElHSUjC6VYHSQxI6CYOaqQMUiZGaqYCVDMxABVzBQoZogiZGgGEgJqjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    Value = #"Changed Type"[Value],
    Loop = List.Generate(
                        ()=>[i=0,j=Value{i},k=j],
                        each [i]<List.Count(Value),
                        each [i=[i]+1, j=Value{i}, k=[k]*3+j*0.5],
                        each[k]
    ),
    Custom1 = Table.FromColumns(Table.ToColumns(#"Changed Type")&{Loop},List.Combine({Table.ColumnNames(#"Changed Type"),{"cumulativeValue"}}))
in
    Custom1

     

     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

It's possible in DAX but complexity is O(n^2) rather than the O(n) in Power Query since you have to calculate each row from the beginning instead of the last row.

 

Cumulative = 
VAR Subtable = FILTER ( Query1, Query1[Date] <= EARLIER ( Query1[Date] ) )
VAR AddIndex = ADDCOLUMNS ( Subtable, "Index", RANKX ( Subtable, Query1[Date],, DESC ) )
VAR MaxIndex = MAXX ( AddIndex, [Index] )
RETURN
    SUMX (
        AddIndex,
        POWER ( 3, [Index] - 1 ) * [Value] * IF ( [Index] = MaxIndex, 1, 0.5 )
    )

 

 

 

smpa01's avatar
smpa01
Community Champion
4 years ago

AlexisOlson   this is insanely awesome to say the least !!! is it kindly possible to explain the code little bit. I have been trying to disect the code to understand what is going on but I am stuck. If you can spare some time to look into this would be great. 

 

If I only do this, I can figure out what the SUMX is doing to the code

 

 

 

Date Value Column
2021-01-01 28 28*1
2021-01-02 7 28*1+7*0.5
2021-01-03 26 28*1+7*0.5+26*0.5
2021-01-04 40 28*1+7*0.5+26*0.5+40*0.5
2021-01-05 1 28*1+7*0.5+26*0.5+40*0.5+1*0.5
2021-01-16 16 28*1+7*0.5+26*0.5+40*0.5+1*0.5+16*0.5

 

I can't however figure out how POWER is getting evalauted in every row. I can't seem to figure out the equation with POWER in each row.

 

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Maybe this will help?

     

    3*(3*(3*(3*(3*a1 + 0.5*a2)+0.5*a3)+0.5*a4)+0.5*a5)+0.5*a6
    = 3^5*a1 + 3^4*0.5*a2 + 3^3*0.5*a3 + 3^2*0.5*a4 + 3^1*0.5*a5 + 3^0*0.5*a6
    = sum_i=6^1 3^(i-1) * (if i = 6 then 1 else 0.5) * a(7-i)

     

     

     

    Maybe I should have gone with the ascending index instead:

     

    Cumulative = 
VAR Subtable = FILTER ( Query1, Query1[Date] <= EARLIER ( Query1[Date] ) )
VAR AddIndex = ADDCOLUMNS ( Subtable, "@Index", RANKX ( Subtable, Query1[Date],, ASC ) )
VAR MaxIndex = MAXX ( AddIndex, [@Index] )
RETURN
    SUMX (
        AddIndex,
        POWER ( 3, MaxIndex - [@Index] ) * [Value] * IF ( [@Index] = 1, 1, 0.5 )
    )