cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Rolling 12 month total

Hi

I am trying to calculate the last rolling 12 months data that i can put into a line chart visual.

It will need to show monthly going back for 12 months as per the below data. So for Sept 23 it will show the sum of data for the October 22 - Sept 23. August 23 will show data for Sept 22 - Aug 23 and so on.

 Total MonthYear Rolling 12 months 40 01/01/2022 78 01/02/2022 136 01/03/2022 275 01/04/2022 360 01/05/2022 463 01/06/2022 522 01/07/2022 612 01/08/2022 687 01/09/2022 761 01/10/2022 838 01/11/2022 887 01/12/2022 5659 906 01/01/2023 6525 951 01/02/2023 7398 974 01/03/2023 8236 908 01/04/2023 8869 880 01/05/2023 9389 876 01/06/2023 9802 890 01/07/2023 10170 887 01/08/2023 10445 881 01/09/2023 10639

This is the calculation i am using but it is showing monthly totals on the chart not the cumulated 12 month total.
Rolling 12 Months =
CALCULATE(
SUM( Total[Total]),
DATESINPERIOD( Date[MonthYear], MAX( Date[MonthYear] ), -12,  MONTH )
1 ACCEPTED SOLUTION
Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

A measure like the following might work for you...

`rollingTotalMeasure =var _rollingMonths =12var _maxDate =MAXX(    ALL('Table'),    [MonthYear])ReturnCALCULATE(    SUM('Table'[Total]),    FILTER(        ALL('Table'),         [MonthYear] <= MAX('Table'[MonthYear]) && DATEDIFF([MonthYear], _maxDate, MONTH) < _rollingMonths    ))`

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors