How to calculate Average member joined in last 30 days

[biswad]

Hi All, 

I have a table which has created on date and membership ID. I would like to calculate the average number of member joined in the last 30 days. Any help on this would be greatly appriciated. 

[biswad]

Thank you all for trying to help me on this problem. There are lot of things to take on, much appriciated everyone's effort. 

[danextian]

Hi @biswad 

I'm not sure how actual data really is but given the sample data, please try these:

30 Days Moving Average = 
VAR _Value =
    CALCULATE (
        SUM ( 'Table'[value] ),
        DATESINPERIOD ( 'Table'[Date], MAX ( 'Table'[Date] ), -30, DAY )
    )
VAR _Count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date] ),
        DATESINPERIOD ( 'Table'[Date], MAX ( 'Table'[Date] ), -30, DAY )
    )
RETURN
    DIVIDE ( _Value, _Count )


30 Days Moving Average 2  = 
VAR _Value =
    CALCULATE (
        SUM ( 'Table'[value] ),
        DATESINPERIOD ( 'Table'[Date], MAX ( 'Table'[Date] ), -30, DAY )
    )
RETURN
    DIVIDE ( _Value, 30)

30 Days Moving Average 3 = 
CALCULATE (
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( 'Table', 'Table'[Date] ),
            "@value", CALCULATE ( SUM ( 'Table'[value] ) )
        ),
        [@value]
    ),
    DATESINPERIOD ( 'Table'[Date], MAX ( 'Table'[Date] ), -30, DAY )
)

Please see attached sample pbix.

[lucadelicio · Accepted solution]

hi @biswad 

try this measure:
Average Members Joined in Last 30 Days =

VAR Last30DaysMembers =

    CALCULATE(

        COUNTROWS(Table1),

        FILTER(

            Table1,

            Table1[Date] >= TODAY() - 30 &&

            Table1[Date] <= TODAY()

        )

    )

RETURN

    DIVIDE(Last30DaysMembers, 30)