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 power bi showing 4month rolling total like this:

i.e. Client 1 Jan value 0f 700 is a total from Jan - Apr value ( 100+200+300+100) and so on. 

My data set containt Clients, Date( From Jan 2022 - Dec 2023 and Value colum. 

I tried a lot of formulas that i can see online but nothing seemed to work. Can anyone help me, please? Thank you so much in advance. 
Cheers,
Vanessa

  • Anonymous's avatar
    Anonymous
    4 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]))

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your dataset.

     

     

     

     

    Expected measure: =
CALCULATE (
    [Value sum measure:],
    'Calendar'[Date] >= MIN ( 'Calendar'[Date] ),
    'Calendar'[Date] <= EOMONTH ( MIN ( 'Calendar'[Date] ), 3 )
)

     

     

     

    • vanessacalalang's avatar
      vanessacalalang
      Frequent Visitor
      4 years ago

      Hi Jihwa, 


      Thanks for your response but I don't think it's the right formula of what I'm looking for. Say if we use value on the table that you have there as example. I need the formula to show the rolling 4month totals like this:

      For Jan-22 = values for (Jan+Feb+Mar+Apr). So for Client1 = 30+30+26+31 hence 117. For Feb = values for (Feb+Mar+Apr+May). So for Client1 = 30+26+31+31 hence 118. It's easy to do it in excel but I can't seem to pathom the right formula in Power BI. Appreciate your help. Thanks!

       

      Cheers,
      Vanessa

       

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Thank you for your feedback.

        I am confused a little bit. I created a sample, and in my sample, Client01 has 6 on January, 9 on Feb, 5 on March, and 10 on Apr. So, 6+9+5+10 = 30

        Do you mean that, for Client01, after you create 4 months rolling, and then do the calculation again for 4 months rolling? 

         

  • vanessacalalang's avatar
    vanessacalalang
    Frequent Visitor
    3 years ago

    Hi Jihwan_Kim, 


    Sorry I missed your raw data. This is exactly what I need except I need to derive the "value" from the measure that I created as there are other calculations into it. Any advise on how can I get the SUM of the "measure" so I can use your brilliant formula? Thansk! 

    Cheers,
    Vanessa