Forum Discussion

vanessacalalang's avatar
vanessacalalang
Frequent Visitor
4 years ago
Solved

4month rolling total

Hi, Can someone helpme please?  I need to create a matrix to show  4months total of sales per client.  From this Sales table ( I just did mock up data in excel) I need to have  matrix in pow...
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI  I add an index column , then calculate it in below formular. then you can delete index column

    = Table.AddColumn(#"Added Index", "Custom", each List.Sum(Table.SelectRows(#"Added Index",(a)=>a[Index]<=[Index]+3 and a[Index]>=[Index] and a[Clients]=[Clients])[Value]))

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

HI  I add an index column , then calculate it in below formular. then you can delete index column

= Table.AddColumn(#"Added Index", "Custom", each List.Sum(Table.SelectRows(#"Added Index",(a)=>a[Index]<=[Index]+3 and a[Index]>=[Index] and a[Clients]=[Clients])[Value]))

 

  • vanessacalalang's avatar
    vanessacalalang
    Frequent Visitor
    3 years ago

    Hi Anonymous ,

    I tried this solution and it worked. Although when I put it in matrix and used the "Custom" value that was created from this formula, the Row Subtotal is not correct. Is there a way to reflect the correct Row Subtotal? Thanks! 

    Cheers,
    Vanessa

    • vanessacalalang's avatar
      vanessacalalang
      Frequent Visitor
      3 years ago

      Hi Youngli 

      Please disregard my innocence. I got it. Thank you so much for your help. 

      Cheers,
      Vanessa