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
    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]))

     

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?