cancel
Showing results for
Did you mean:

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

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.

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

 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

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.

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

Helper I

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.

Super User

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
"Flag",
each
if [Index] < 5 then
0
else
List.Accumulate(
{[Index] - 4 .. [Index]},
0,
(state, current) =>
state + 1
state - 1
else
0
)
)
in
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
]
}
}
),
#"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"

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors