topic Re: 4month rolling total in DAX Commands and Tips

4month rolling total

vanessacalalang — Mon, 12 Sep 2022 03:19:32 GMT

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

Re: 4month rolling total

Jihwan_Kim — Mon, 12 Sep 2022 06:30:14 GMT

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

Re: 4month rolling total

vanessacalalang — Tue, 13 Sep 2022 00:20:00 GMT

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

Re: 4month rolling total

Jihwan_Kim — Tue, 13 Sep 2022 03:05:14 GMT

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?

Re: 4month rolling total

Anonymous — Tue, 13 Sep 2022 07:24:31 GMT

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

Re: 4month rolling total

vanessacalalang — Thu, 15 Sep 2022 23:37:24 GMT

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

Re: 4month rolling total

vanessacalalang — Fri, 16 Sep 2022 03:23:11 GMT

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

Re: 4month rolling total

vanessacalalang — Fri, 16 Sep 2022 03:27:32 GMT

Hi Youngli

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

Cheers,
Vanessa