How to show Member count over a period of time

[Stabbathehut]

Hi All, 

I am struggling to understand how I have made this so difficult. I am trying to show by year, month and week how many members are joining in a certain community but am just getting various errors. The attempt is to make a cumulative frequency scatter graph that then forecasts how long it will take to reach 10k members. Any help would be greatly appreciated 🙂

[v-shex-msft · Accepted solution]

Hi @Stabbathehut ,

You can try to use the following dax formulas if they suitable for your requirement.

Calculated table calendar:

Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Measures:

Rolling total =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Date] <= MAX ( 'Calendar'[Date] ) ),
    VALUES ( 'Table'[Week] )
)

Rolling total Over 10k Duartion(days) =
VAR temp =
    SUMMARIZE ( 'Calendar', 'Calendar'[Date], "Total", [Rolling total] )
VAR exceed =
    MINX ( FILTER ( temp, [Total] >= 10000 ), [Date] )
RETURN
    DATEDIFF ( MIN ( 'Calendar'[Date] ), exceed, DAY )

Regards,

Xiaoxin Sheng

[Ashish_Mathur · Accepted solution]

Hi,

Build a Calendar table by going to Data > Modelling > New Table and writing this measure

Calendar = CALENDAR(MIN(Data[Date]),MAX(Data[Date]))

Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  In the Calendar Table, extract the Year and Month by using the following calculated column formulas

Year = YEAR(Calendar[Date])

Month = FORMAT(Calendar[Date],"mmmm")

To your visual, drag any date dimension (Day/Month/Year) only from the Calendar Table.  Write these measures

New members = SUM(Data[New Members per week])

Members till date = CALCULATE([New members],DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar),Calendar[Date]),MAX(Calendar[Date])))

Hope this helps.

[v-shex-msft · Accepted solution]

Hi @Stabbathehut ,

You can try to use the following dax formulas if they suitable for your requirement.

Calculated table calendar:

Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Measures:

Rolling total =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Date] <= MAX ( 'Calendar'[Date] ) ),
    VALUES ( 'Table'[Week] )
)

Rolling total Over 10k Duartion(days) =
VAR temp =
    SUMMARIZE ( 'Calendar', 'Calendar'[Date], "Total", [Rolling total] )
VAR exceed =
    MINX ( FILTER ( temp, [Total] >= 10000 ), [Date] )
RETURN
    DATEDIFF ( MIN ( 'Calendar'[Date] ), exceed, DAY )

Regards,

Xiaoxin Sheng