Forum Discussion

Deivis_IMIQ's avatar
Deivis_IMIQ
Frequent Visitor
4 years ago
Solved

Calculate a time difference based on criteria

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

Need Help
Tips and Tricks
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    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/

     

     

     

     

3 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    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's avatar
    David-Ganor
    Resolver II
    4 years ago

    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

  • Deivis_IMIQ's avatar
    Deivis_IMIQ
    Frequent Visitor
    4 years ago

    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 ğŸ™‚