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 w...
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/

     

     

     

     

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