Calculate a time difference based on criteria

[Deivis_IMIQ]

I am trying to get in one column, the last date that a criteria matched, or even the time difference between them, for the same product. in the table I will try to explain what I need. (I am trying with FILTER, Calculate, Intermediate Measures, but I cannot get something out of it, I am not specialist, so I need some help) 

In the example, let's say that I want to trigger the result when the amount of increase of Kilograms is by 5Kg (more than 5 Kg by product "N"), increased per day:

Product		Date		Increased Kg		Result (expected)
A		01/05/2022		6,0		0 days (01/05)
B		01/05/2022		2,0		3 days (01/03)
A		01/04/2022		2,0		3 days (01/02)
B		01/04/2022		3,0		2 days (01/03)
A		01/03/2022		2,5		3 days (01/01)
B		01/03/2022		4,5		3 days (01/01)
A		01/02/2022		2,2		2 days (01/01)
B		01/02/2022		0,5		1 days (01/01)
A		01/01/2022		9,0		0 days (01/01)
B		01/01/2022		6,0		0 days (01/01)
A		12/31/2021		5,5		0 days (12/31)
B		12/31/2021		7,0		0 days (12/31)

So I hope I could make this understandable. The Idea, is that I want to know in the past, exactly when the criteria is matched (increased more than 5 Kg each product). 🙂

[Deivis_IMIQ]

Just one word: PERFECT !!!! Exactly What I was trying to implement... but to be honest, I was really far away for such a solution... Thanks a lot @tackytechtom 🙂

[tackytechtom · Accepted solution]

Hi @Deivis_IMIQ ,

I'll give it a shot:

This is the code for the calculated column:

TomsResultColumn = 
IF ( 
    Table[Increased Kg] >= 5, 
    "0 days (" & Table[Date].[Date] & ")" , 
    DATEDIFF( 
        CALCULATE ( 
            MAX ( Table[Date] ), 
            ALLEXCEPT (Table, Table[Product]), 
            Table[Increased Kg] >= 5, 
            Table[Date] < EARLIER ( Table[Date] )
        ),
        Table[Date].[Date], 
        DAY 
    ) & " days (" & 
    CALCULATE ( 
        MAX ( Table[Date] ), 
        ALLEXCEPT (Table, Table[Product]), 
        Table[Increased Kg] >= 5, 
        Table[Date] < EARLIER ( Table[Date] )
    )  & ")"
)

Hope this helps! 🙂 

/Tom

https://www.tackytech.blog/

https://www.instagram.com/tackytechtom/

[David-Ganor]

Hi @Deivis_IMIQ 

Please create a calculated column as follows:

Recent_Increse=

Var this_row_product=Table[Product]

Return

Calculate(

MAX(Table [Date])

,Filter(Table

,Table[Product]=this_row_product

&&

Table[Increased KG]>=5)

)

You can use Datediff afterwards.

Hope it helps

David