DAX measure for a running 38month calculation

[Micky1968]

Dear all,

I am struggeling here with a running 38 months calculation.
I need the Acumulated sales column from month 39 onwards (add sales from month 39 and substract acumulated sales from month 1), month 40 (add sales from month 40 and substract acumulated sales from month 2) and so on.
I have a date table. 
Can someone help ? 
Thanks so much
Michael

[Micky1968]

Dear Selva-Salimi,

Thank you for your tip. This worked for me as a between calculation 🙂

Great.

Thanks again

Michael

[johnt75]

You could create a measure like

Rolling 38 month accumulated =
IF (
    SELECTEDVALUE ( 'Table'[Month] ) > 38,
    VAR Months =
        WINDOW (
            -37,
            REL,
            0,
            REL,
            ALL ( 'Table'[Month] ),
            ORDERBY ( 'Table'[Month], ASC )
        )
    VAR Result =
        CALCULATE ( SUM ( 'Table'[Accumulated Sales] ), Months )
    RETURN
        Result
)

[mdaatifraza5556]

Hi @Micky1968 

Could you please create calculated column using below DAX ?

Rolling_Accumulated_Sales =

VAR CurrentMonth = 'Table'[Months]

VAR CurrentAccumulatedSales = 'Table'[Accumulated Sales]

VAR PriorAccumulatedSales =

    CALCULATE(

        MAX('Table'[Accumulated Sales]),

        FILTER(

            'Table',

            'Table'[Months] = CurrentMonth - 38

        )

    )

RETURN

    IF (

        CurrentMonth <= 38,

        BLANK(),

        CurrentAccumulatedSales - PriorAccumulatedSales

    )

If this answers your questions, kindly accept it as a solution and give kudos

[Micky1968]

Dear mdaatifraza5556

Thank you for your help.

In your Formula, to which Table you refer (Red marked)

Thank you
Michael

Rolling_Accumulated_Sales =
VAR CurrentMonth = 'Table'[Months]
VAR CurrentAccumulatedSales = 'Table'[Accumulated Sales]
VAR PriorAccumulatedSales =
CALCULATE(
MAX('Table'[Accumulated Sales]),
FILTER(
'Table',
'Table'[Months] = CurrentMonth - 38
)
)
RETURN
IF (
CurrentMonth <= 38,
BLANK(),
CurrentAccumulatedSales - PriorAccumulatedSales
)

[mdaatifraza5556]

Hi @Micky1968 

the data you provided based on that i have create a single table name "table"



and used this table for result ( In the same table I have create a calculated column)

The data u have provided is a raw data or it is a table visual ? because i have assume it's your table 

If this answer you question, kindly accept it as a solution and give kudos.

[Micky1968]

I am using this DAX measure formula in the Acumulates Sales field:

Acumulative sale =

CALCULATE (

    SUM ( 'fsm product'[quantity]),

    FILTER (

        ALL ( 'Datum' ), 

        'Datum'[Date] <= MAX ( 'Datum'[Date])

    )

)

[Selva-Salimi · Accepted solution]

Hi @Micky1968 

You can rewrite your measure as follows: 

Acumulative sale =

CALCULATE (

    SUM ( 'fsm product'[quantity]),

    FILTER (

        ALL ( 'Datum' ), 

        'Datum'[Date] <= selectedvalue ( 'Datum'[Date]) -38

    )

)

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.