Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rolling 2 Month average

Please help me with the DAX formula. Objective is to get 3 months average for Inventory only.

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    You can create a measure as below, please find the attachment for the details.

     

    Average 3 months inventory = 
VAR LastDate_ =
    LASTDATE ( 'Table'[Date] )
VAR Rolling2months =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Nature] = SELECTEDVALUE ( 'Table'[Nature] )
                && 'Table'[Nature] = "Inventory"
                && 'Table'[Date] <= LastDate_
                && 'Table'[Date] > DATEADD ( LastDate_, -3, MONTH )
        )
    )
VAR Countofmonth =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Month] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] <= LastDate_
                && 'Table'[Date] > DATEADD ( LastDate_, -3, MONTH )
        )
    )
RETURN
    DIVIDE ( Rolling2months, Countofmonth, 0 )

     

    Best Regards

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , You can try measure like these with date table

     

    Rolling 2 = divide( CALCULATE(sum(Sales[Amout]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
    CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-2,MONTH), filter(Sales,not(isblank(sum(Sales[Amout]))))))

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    is this your rawdata table? do you want to create a column or measure?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    You can create a measure as below, please find the attachment for the details.

     

    Average 3 months inventory = 
VAR LastDate_ =
    LASTDATE ( 'Table'[Date] )
VAR Rolling2months =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Nature] = SELECTEDVALUE ( 'Table'[Nature] )
                && 'Table'[Nature] = "Inventory"
                && 'Table'[Date] <= LastDate_
                && 'Table'[Date] > DATEADD ( LastDate_, -3, MONTH )
        )
    )
VAR Countofmonth =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Month] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] <= LastDate_
                && 'Table'[Date] > DATEADD ( LastDate_, -3, MONTH )
        )
    )
RETURN
    DIVIDE ( Rolling2months, Countofmonth, 0 )

     

    Best Regards