Forum Discussion

Jakob_BI's avatar
Jakob_BI
Helper I
3 years ago

Last available date value vs current date value increase or decrease

Hi all,     Step 1. I need help to calculate if there is an increase or decrease in value between the latest dates for a given batch on a specific product.   Step 2. I need to count if there i...
Need Help
lbendlin's avatar
lbendlin
Super User
3 years ago
if there is an increase or decrease in value between the latest dates for a given batch on a specific product.

The results of your sample are inconclusive as you seem to have shown only unique combinations of batch and product. Please validate

Jakob_BI's avatar
Jakob_BI
Helper I
3 years ago

That is also correct there is only 1 unique batch per product always.

It's a pharma company who produces medicine and for each batch of drug substance there is a bunch of products created. The number of products per batch is captured in another column called "quantity".

This analysis is to help detect if the potent active medicine is at the right intensity level. It must fall within certain threshold and calculating if there is an increase or decrease in the intensity level between each batch is important because 5+ batches with consecutive same change in intensity is no longer an incident, but a pattern that need to be addressed.

this sample data is just to show that for product B there is no pattern detected, but there is for product A.

does it make sense? thanks for your reply  

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    I added some more sample data to cover more scenarios. It's still not a lot.  Anyway - here is the Power Query.  Any value of 5 and above or -5 and below indicates the streak length.

     

     

    let
  fx = (tb) =>
    let
      #"Added Index" = Table.AddIndexColumn(tb, "Index", 0, 1, Int64.Type),
      #"Added Custom" = Table.AddColumn(
        #"Added Index",
        "Flag",
        each
          if [Index] < 5 then
            0
          else
            List.Accumulate(
              {[Index] - 4 .. [Index]},
              0,
              (state, current) =>
                if #"Added Index"[value]{current} > #"Added Index"[value]{current - 1} then
                  state + 1
                else if #"Added Index"[value]{current} < #"Added Index"[value]{current - 1} then
                  state - 1
                else
                  0
            )
      )
    in
      #"Added Custom",
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "fc/NDYAgDIDRXThjAkX8OVrUJQj7ryE1VoJUEumBF8zXGNWm9H2sycPYIX9gwKmko0I2S8NVFtggD/CV7Wzu+ScUO9hGMqjsZPNkvjK6R24ROpF3EDrJVjkTeQUhE98V2szyrs0MnczQyQz/mUSLXEk0y5FE06cxXQ==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [batch = _t, Product = _t, value = _t, #"purification date" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"purification date", type date}}, "en-GB"),
  #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type", {{"value", Int64.Type}}),
  #"Grouped Rows" = Table.Group(
    #"Changed Type1",
    {"Product"},
    {
      {
        "Rows",
        each _,
        type table [
          batch = nullable text,
          Product = nullable text,
          value = nullable number,
          purification date = nullable date
        ]
      }
    }
  ),
  #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Checked", each fx([Rows])),
  #"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"Checked"}),
  #"Expanded Checked" = Table.ExpandTableColumn(
    #"Removed Other Columns",
    "Checked",
    {"batch", "Product", "value", "purification date", "Index", "Flag"},
    {"batch", "Product", "value", "purification date", "Index", "Flag"}
  )
in
  #"Expanded Checked"