Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@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}})
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