Rolling sum next 12 months of sales

[dannywallis1994]

Hi there, I have the following data and I am looking to create a measure to sum the next 12 months worth of sales. 

The calculation I am looking for will be rolling and start from the year month. I have added my desired output below. 

Any help would be appreciated.

DATA TABLE
Date	Revenue
01/01/2022	100
01/01/2022	95
01/02/2022	90
01/03/2022	85
01/04/2022	80
01/05/2022	75
01/06/2022	70
01/07/2022	65
01/08/2022	60
01/09/2022	55
01/10/2022	50
01/11/2022	45
01/12/2022	40
01/01/2023	35
01/02/2022	30

OUTPUT
Month	Revenue
Jan-22	910
Feb-22	780

Best, 

[Roxanne_pbi_]

Hi,
Quite new here so I am not sure if I am allowed to reply to an existing solved topic, but I will try.
Trying to get a similar calculation going, a rolling sum of coming x amount of months, le's say 12.
I followed the above advice and keep getting stuck at this calulation 

Next 12 month FC =

CALCULATE (

    SUM ( 'F1 2023 Totals'[Forecast quantity] ),

    DATESINPERIOD (

        'DimDateQry'[Date],

        EOMONTH ( MAX ( DimDateQry'[Date] ), -1 ) + 1,

        12,

        Month

    )

)

Which gives me this error:
The following syntax error occurred during parsing: Invalid token, Line 6, Offset 35, '[Date] ), -1 ) + 1, 12, Month ))).

Would someone be able to help me please? Happy to offer more context if needed.

[v-jingzhang · Accepted solution]

Hi @dannywallis1994 

You can add a Date table to your model and create a relationship from Date table to Sales table on date columns. In Date table, add Month column with formula Month = FORMAT([Date],"mmm-yy"). Put this column and the following measure into a table visual. 

Next 12 month Revenue = 
CALCULATE (
    SUM ( 'Table'[Revenue] ),
    DATESINPERIOD (
        'Date'[Date],
        EOMONTH ( MAX ( 'Date'[Date] ), -1 ) + 1,
        12,
        MONTH
    )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@dannywallis1994 , Create a measure like this with help from date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),12,MONTH))

or

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MIn('Date'[Date ]),12,MONTH))

[Roxanne_pbi_]

Hi @amitchandak 
I tried this solution as well: 

SumRolling 12 = CALCULATE(sum('F1 2023 Totals'[Forecast quantity]),DATESINPERIOD('DimDateQry'[Date],MIN('DimDateQry'[Date]),12,MONTH))

But the result I get is not a rolling sum but the actual forecast value for the month, see pic. Any idea what I'm doing wrong?