4month rolling total

[vanessacalalang]

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

[vanessacalalang]

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

[Anonymous · Accepted solution]

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]

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]

Hi Youngli 

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

Cheers,
Vanessa

[Jihwan_Kim]

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]

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]

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?