Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Jakob_BI
Helper I
Helper I

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

 

batchProductvaluepurification dateCalculate: Increase or decrease between latest available date for batchCalculate: 5 increases or decreases consecutive
AA1001-01-202300
BA1113-01-202310
CA1225-01-202310
DA1301-02-202310
EA1402-02-202310
FA1505-02-202311
AB1101-01-202300
BB1013-01-2023-10
CB925-01-202310
DB1201-02-202310
EB1302-02-202310
FB1205-02-2023-10
5 REPLIES 5
Jakob_PBI
Frequent Visitor

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).

 

batchProductvaluepurification dateCalculate: Increase or decrease between latest available date for batchCalculate: 5 increases or decreases consecutive
AA1001-01-202300
BA1113-01-202310
CA1225-01-202310
DA1301-02-202310
EA1402-02-202310
FA1505-02-202311
AB1101-01-202300
BB1013-01-2023-10
CB925-01-2023-10
DB1201-02-202310
EB1302-02-202310
FB1205-02-2023-10

 

 

Jakob_BI2
Regular Visitor

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.

 

Jakob_BI2_2-1685014004027.png

 

 

lbendlin
Super User
Super User

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

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  

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"

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.