Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Running total working backwards over time

  The above table gives a rough idea of my problem. I queried in data from SQL from two different tables, a current value snapshot table and an event table.  I know what my current value is, b...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may create two columns to get the value.

    Column =
IF (
    Table1[DateTime] = MAXX ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[DateTime] ),
    Table1[Current Quantity] - Table1[Quant Add/Remove],
    Table1[Quant Add/Remove] * ( -1 )
)

    Column 2 =
CALCULATE (
    SUM ( Table1[Column] ),
    FILTER (
        ALL ( Table1 ),
        Table1[ID] = EARLIER ( Table1[ID] )
            && Table1[DateTime] >= EARLIER ( Table1[DateTime] )
    )
)

    Regards,

    Cherie

  • smpa01's avatar
    smpa01
    7 years ago

    M solution

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIFYmMQ1jc01TcyMLRUMDSwMjVVitVByOsaoSowtDIwQFFgiCGPZoAxugJDiAEgg4FMHSUjHE6AK9DFtAO/EXBH4DQCYkksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, CQ = _t, Value = _t, #"Date Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"CQ", Int64.Type}, {"Value", Int64.Type}, {"Date Time", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CQ"}, {{"AD", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn(
Table.Sort([AD],{{"Date Time", Order.Descending}}),"Rank",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AD"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "CQ", "Value", "Date Time", "Rank"}, {"ID", "CQ.1", "Value", "Date Time", "Rank"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"CQ"}, {{"AD", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each let
   Source =[AD],
   Add_Row = Table.InsertRows(Source,0,{[CQ=null, ID=null, CQ.1=null, Value=List.First(Source[CQ]), Date Time=null, Rank=null]})
 in 
   Add_Row),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"AD"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Custom.1", each let
   Source=[Custom],
   Custom1 = List.Accumulate(Source[Value],{0},(state,current)=>if current=Source[Value]{0} then {current} else state&{List.Last(state)-(current)}),
    Custom2 = Table.FromColumns(Table.ToColumns(Source)&
{Custom1}),
 Remove= Table.RemoveRows(Custom2,0)
in
    Remove),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Custom", "CQ"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns2", "Custom.1", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Custom.1.Column1", "Custom.1.Column2", "Custom.1.Column3", "Custom.1.Column4", "Custom.1.Column5", "Custom.1.Column6", "Custom.1.Column7"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.1",{{"Custom.1.Column1", "ID"}, {"Custom.1.Column2", "CQ"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns",{"Custom.1.Column3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{"Custom.1.Column4", "Q A/R"}, {"Custom.1.Column5", "Date Time"}, {"Custom.1.Column6", "Rank"}, {"Custom.1.Column7", "Desired Running Total"}})
in
    #"Renamed Columns1"
v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous 

 

Could you explain more about your expected output?How to calculated the value 6,9,7....?For example:You may get the next quant with below calculated column.

Next Quant = 
CALCULATE (
    SUM ( Event[Quant Add/Remove] ),
    FILTER (
        ALLEXCEPT ( Event, Event[ID] ),
        Event[Index]
            = EARLIER ( Event[Index] ) + 1
    )
)

Regards,

Cherie

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I apologize for the lack of clarity in my post. 

 

 

 

Each ID has a current quantity amount which acts as a snapshot for the current amount of that ID (this is why current quantity remains constant.) From there, I am seeking to work back in time using the amount that was added or removed from the quant add/remove column.  Thus, the most recent add/remove for ID 1 occurred at 11:10 on 3/15. We see that 3 was removed, thus 5-(-3)=8 was in before this transaction. The next before that was at 11:05 where 1 was added, thus we subtract that from the prior amount (8)-(1)=7. Then before that at 11 we removed 2, so the prior amount of (7)-(-2)=9. I need to perform this function for each ID so I can help track the amount per ID over time when rolling totals are not being calculated along with transaction amounts. Does this help v-cherch-msft ? Thank you for your willingness to help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Here is what I came up with SQL side, although it does not work in the way I intended yet:

     

    MIN("Current Quantity") OVER(Partition BY ID)-
    SUM("Quant Add/Remove") OVER(Partition by ID order by DateTime) as 'Running Total Expected Output'

     

     

    Problematically, this BEGINS the running total at the current amount rather than ENDING it there. Just at a loss at the moment.

    • v-cherch-msft's avatar
      v-cherch-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      Hi Anonymous 

      You may create two columns to get the value.

      Column =
IF (
    Table1[DateTime] = MAXX ( ALLEXCEPT ( Table1, Table1[ID] ), Table1[DateTime] ),
    Table1[Current Quantity] - Table1[Quant Add/Remove],
    Table1[Quant Add/Remove] * ( -1 )
)

      Column 2 =
CALCULATE (
    SUM ( Table1[Column] ),
    FILTER (
        ALL ( Table1 ),
        Table1[ID] = EARLIER ( Table1[ID] )
            && Table1[DateTime] >= EARLIER ( Table1[DateTime] )
    )
)

      Regards,

      Cherie