DAX Calculation of Survival Rates

[khesan99]

Hi

I am relatively new to DAX and have a problem that is causing me trouble.  This is not the actual scenario for confidentiality reasons.

I need to calculate the [Survival_Rate].

We are a nursery that plants a number of plants each month.  And each month there are a number of plants that die. 

I need to calculate [Survival_Rate] over a period of 30 months (could be days, months or years, this is purely an index column).  The [Survival_Rate] is for each month what percentage are still alive.  For example at Month 20 there are  250 plants alive and 20 died.

The [Plant Death Rate] is calculated by [Number Plants Died]/[Number of Plants Planted]
Survival_Rate is to be calculated as follows:

• The [Survival_Rate] for the [RowIndex] = 0  is 100
• For [RowIndex] = 1, it is 100*(1-[PlantDeathRate] from RowIndex=0)
• For [RowIndex] = 2, it is  [Result From RowIndex = 1 above) * (1-[PlantDeathRate from RowIndex = 1])

The table below has some sample data with the correct calculations.

RowIndex	Number of Plants Planted	Number of Plants Died	Plant Death Rate	Survival Rates
0	282	28	9.9%	100.0%
1	314	25	8.0	90.1
2	290	6	2.1	82.9
3	303	12	4.0	81.2
4	300	13	4.3	78
5	292	7	2.4	74.6
6	254	28	11.0	72.8

Thanks in advance for your help.

[khesan99]

wow, thanks Iblendin, this is awesome !  Sincere thanks also to pointing out that DAX was not the best idea.

[lbendlin · Accepted solution]

DAX is not necessarily the right tool for that, although you can use PRODUCTX to some extent.  Better to do this in Power Query,

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "LY3JEcAwCAN74e2HOewktTDuv41EO/lo0CJBt00bFnegdkabf2N6CSwAu0e5jU8FptQDUAAFPAGLhnoXfnOs/h/nBQ==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [RowIndex = _t, #"Number of Plants Planted" = _t, #"Number of Plants Died" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"RowIndex", Int64.Type},
      {"Number of Plants Planted", Int64.Type},
      {"Number of Plants Died", Int64.Type}
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Plant Death Rate",
    each [Number of Plants Died] / [Number of Plants Planted],
    type number
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom",
    "Survival Rate",
    each List.Accumulate(
      {0 .. [RowIndex]},
      100,
      (state, current) =>
        if current = 0 then state else state * (1 - #"Added Custom"[Plant Death Rate]{current - 1})
    ),
    type number
  )
in
  #"Added Custom1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".