Forum Discussion

Ahak's avatar
Ahak
Frequent Visitor
3 years ago
Solved

DAX Calculate Duration

Hi  

 

I have a table which shows production time. 

 

Always when I get new produced product i get a new entry. I have column with an category and this category can change over the the day couple times. So they change the product which is produced.  I want to calculate the duration between this changes. 

The table looks like this:

 

 

I want to calculate the duration between the ID 5 and 6, 8 and 9 and 13 and 14. 
Maybe additional table which Summarize it by Number of changes and gets the start date and the Enddate? 
How can I extract this timestamps? 

Thank´s!

Need Help
  • Ahak's avatar
    Ahak
    3 years ago

    Hi nvprasad 

     

    I Could it solve it by myself. 

     

    many thanks, anyway. 

    DurationNew7 = 
VAR CurrentRowID = tabelle2[ID]
VAR CurrentCategory = tabelle2[Category (Text)]
VAR PreviousRowID = CurrentRowID - 1
VAR PreviousRowCategory = 
    CALCULATE(
        MIN(tabelle2[Category (Text)]), 
        FILTER(tabelle2, tabelle2[ID] = PreviousRowID)
    )
VAR PreviousRowTime = 
    CALCULATE(
        MAX(tabelle2[DateandTime]), 
        FILTER(tabelle2, tabelle2[ID] = PreviousRowID)
    )
RETURN 
    IF(
        PreviousRowCategory <> CurrentCategory && NOT(ISBLANK(PreviousRowCategory)),
        (tabelle2[DateandTime] - PreviousRowTime) * 24 * 60, 
        BLANK() 
    )

     

4 Replies

  • nvprasad's avatar
    nvprasad
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi Ahak,

     

    Could you please provide some sample data?

     

    Appreciate a Kudos! ‌‌
    If this helps and resolves the issue, please mark it as a Solution! ‌‌

    Regards,
    N V Durga Prasad

  • Ahak's avatar
    Ahak
    Frequent Visitor
    3 years ago

    Hi nvprasad 

     

    I Could it solve it by myself. 

     

    many thanks, anyway. 

    DurationNew7 = 
VAR CurrentRowID = tabelle2[ID]
VAR CurrentCategory = tabelle2[Category (Text)]
VAR PreviousRowID = CurrentRowID - 1
VAR PreviousRowCategory = 
    CALCULATE(
        MIN(tabelle2[Category (Text)]), 
        FILTER(tabelle2, tabelle2[ID] = PreviousRowID)
    )
VAR PreviousRowTime = 
    CALCULATE(
        MAX(tabelle2[DateandTime]), 
        FILTER(tabelle2, tabelle2[ID] = PreviousRowID)
    )
RETURN 
    IF(
        PreviousRowCategory <> CurrentCategory && NOT(ISBLANK(PreviousRowCategory)),
        (tabelle2[DateandTime] - PreviousRowTime) * 24 * 60, 
        BLANK() 
    )