how to calculate future 6 months values

[Prasala583]

Hello All,

I have a requirement to show the sum of future 6 months from the selected month
let’s says we are selecting October then I need to calculate from Nov, Dec, Jan, Feb, March, Apr
Thanks
Prashanth

[v-stephen-msft · Accepted solution]

Hi @Prasala583 ,

According to your description, you want to calculate the sum of the following six months based on the selected month. But I think you are missing the element of year.

Here's my solution.

Sample data

1.Create a calendar table. There's no relationship between tables.

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
    "YearMonth", FORMAT ( [Date], "YYYY-MM" )
)

2.Create a measure.

Sum of next 6 months = 
VAR _mon =
    VALUE ( RIGHT ( SELECTEDVALUE ( 'Calendar'[YearMonth] ), 2 ) )
VAR _yea =
    VALUE ( LEFT ( SELECTEDVALUE ( 'Calendar'[YearMonth] ), 4 ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        DATESINPERIOD ( 'Table'[Date], DATE ( _yea, _mon + 1, 1 ), 5, MONTH )
    )

3.Create a slicer with YearMonth field, a table visual with ID fild and the measure. This is the result.

Check the attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Prasala583 , With help from date table and time intelligence

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

but if you want a trend, you need independent table 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI