Last available date value vs current date value increase or decrease

[Jakob_BI]

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 is 6 or more increases or decreases in the given context of batch and product.

batch	Product	value	purification date	Calculate: Increase or decrease between latest available date for batch	Calculate: 5 increases or decreases consecutive
A	A	10	01-01-2023	0	0
B	A	11	13-01-2023	1	0
C	A	12	25-01-2023	1	0
D	A	13	01-02-2023	1	0
E	A	14	02-02-2023	1	0
F	A	15	05-02-2023	1	1
A	B	11	01-01-2023	0	0
B	B	10	13-01-2023	-1	0
C	B	9	25-01-2023	1	0
D	B	12	01-02-2023	1	0
E	B	13	02-02-2023	1	0
F	B	12	05-02-2023	-1	0

[Jakob_PBI]

i added the power bi file here to show the desired result:

https://www.dropbox.com/s/55k7iv3sdx8wrcp/PBI%20problem%20file.pbix?dl=0


I still need help to calculate those 2 columns.

I can see there was 1 wrong data in the original datasample. Here is the updated sample (just 1 cell correction).

batch	Product	value	purification date	Calculate: Increase or decrease between latest available date for batch	Calculate: 5 increases or decreases consecutive
A	A	10	01-01-2023	0	0
B	A	11	13-01-2023	1	0
C	A	12	25-01-2023	1	0
D	A	13	01-02-2023	1	0
E	A	14	02-02-2023	1	0
F	A	15	05-02-2023	1	1
A	B	11	01-01-2023	0	0
B	B	10	13-01-2023	-1	0
C	B	9	25-01-2023	-1	0
D	B	12	01-02-2023	1	0
E	B	13	02-02-2023	1	0
F	B	12	05-02-2023	-1	0

[Jakob_BI2]

You dont need more sample data. 

just need to create 2 measures or 2 columns that calculate those 2 highlighted columns.

the first column need to compare current value vs last value for that given product, looking at the purification date for the last date where there was a value for that product.

[lbendlin]

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]

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]

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"