Best way to calculate retention

[MrAtkinson]

Hi all, 

I have a list of data that will grow each month by two rows, the first will be the number of members in the database one the 1st of the month and the 2nd will be the number of member at the end of the month. This will be each month going forward and will give me a file that looks like this

I am trying to figure out the best way to be able to give me the a new column or file that will show me the retention rate for each month

I did try the following code but it just gave me a column with Null for some reason.

=if [[Month Name]] = "May" then [[#"[CountSubscription_Status]"]] - List.First(List.Skip([[#"[CountSubscription_Status]"]],1)) else null

Open to options/ideas. I need to then feed this into a power bi report/graph

Thanks in advance

[dufoq3 · Accepted solution]

Hi @MrAtkinson, like this?

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDVNzIwMlHSUTI1NzFVitWJVjJGFQVSIFGQWjMMUWMDFFFjM6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, CountSubscription_Status = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Source.Name", type date}, {"CountSubscription_Status", Int64.Type}}, "sk-SK"),
    Ad_YearMonth = Table.AddColumn(ChangedType, "YearMonth", each Date.Year([Source.Name])*100 + Date.Month([Source.Name]), Int64.Type),
    GroupedRows = Table.Group(Ad_YearMonth, {"YearMonth"}, {{"Retention Rate", each [CountSubscription_Status]{1} - [CountSubscription_Status]{0}, Int64.Type}})
in
    GroupedRows

[Anonymous · Accepted solution]

@MrAtkinson here is the simple way to calculate retention rate 
 firstly group by the month name then index 1 - index 0 

USE THIS 

= Table.Group(#"Promoted Headers", {
"Month Name"}, {{"Retention Rate", each [countSubscription_Status]{1} - [countSubscription_Status]{0}, Int64.Type}})

[dufoq3 · Accepted solution]

Hi @MrAtkinson, like this?

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDVNzIwMlHSUTI1NzFVitWJVjJGFQVSIFGQWjMMUWMDFFFjM6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, CountSubscription_Status = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Source.Name", type date}, {"CountSubscription_Status", Int64.Type}}, "sk-SK"),
    Ad_YearMonth = Table.AddColumn(ChangedType, "YearMonth", each Date.Year([Source.Name])*100 + Date.Month([Source.Name]), Int64.Type),
    GroupedRows = Table.Group(Ad_YearMonth, {"YearMonth"}, {{"Retention Rate", each [CountSubscription_Status]{1} - [CountSubscription_Status]{0}, Int64.Type}})
in
    GroupedRows