none
Duration problem (gap) RRS feed

  • Question

  • Hello everyone,

    I'm stuck with a duration calculation. I want to calculate the time a GSM is not joinable (in Label with text contains "non joignable").

    The main reason I ask help it's because I don't know how to tell him to start at a specific position (see picture below).

    Currently, the calculation is done on the green line, but I need it to be done on the red one.
    I need to start from the first false state (after a true) until the next true state. 

    You can see image here : https://i.ibb.co/tD36p00/pbi.pn g

    For the duration calculation I use the following columns :
    - App_DateTime
    - Joignabilité ==> (True/False or 1/0)
    - Site ==> There are many sites. Here I show you only one for a better and simple visualization

    The duration (Durée) is in seconds and it's an integer.

    The DAX formula (column) of the duration:

    
    Durée =
    
    IF (
        'JournalPing'[Joignabilité] = TRUE;
        DATEDIFF (
            CALCULATE (
                MAX ( 'JournalPing'[App_DateTime] );
                FILTER (
                    ALLEXCEPT ( 'JournalPing'; 'JournalPing'[Site] );
                    'JournalPing'[App_DateTime] < EARLIER ( 'JournalPing'[App_DateTime] )
                )
            );
            'JournalPing'[App_DateTime];
            SECOND
        );
        0
    )

    Any tips or advice ?
    Thanks.

    Wednesday, January 22, 2020 2:16 PM

Answers

  • Thanks, the sample data file helped (it's hard to write DAX against a screenshot). I just had to change the calculation for the _nextTrue to use a MAX instead of MIN to fix the issue. But changing the RETURN statement to return each of the variables I could see that the _nextDate was not calculating what it should have been.

    Durée 2 =
    VAR _thisDate = 'JournalPing'[App DateTime]
    VAR _nextTrue =
    CALCULATE (
    MAX ( 'JournalPing'[App DateTime] ),
    FILTER (
    ALLEXCEPT ( 'JournalPing', 'JournalPing'[Site] ),
    'JournalPing'[App DateTime] < _thisDate
    && 'JournalPing'[Joignability] = TRUE
    )
    )
    VAR _earliestFalse =
    CALCULATE (
    MIN ( 'JournalPing'[App DateTime] ),
    FILTER (
    ALLEXCEPT ( 'JournalPing', 'JournalPing'[Site] ),
    'JournalPing'[App DateTime] < _thisDate
    && 'JournalPing'[App DateTime] > _nextTrue
    && 'JournalPing'[Joignability] = False
    )
    )
    VAR _result =
    IF (
    'JournalPing'[Joignability] = True,
    DATEDIFF ( _earliestFalse, _thisDate, SECOND ),
    0
    )
    RETURN
    _result


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by CalloudB Thursday, January 30, 2020 12:58 PM
    Thursday, January 30, 2020 11:46 AM
    Moderator

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Filtered Duration.
    Three ways:
    1. Power Query with M code.
    2. Reverse-Linked Table with formulas.
    3. PowerPivot with DAX code.
    http://www.mediafire.com/file/9mnqhhkh147p6sa/01_24_20.xlsx/file
    http://www.mediafire.com/file/9mnqhhkh147p6sa/01_24_20.xlsx/file

    Sunday, January 26, 2020 4:32 AM
  • Thanks for the time and answer.

    I don't understand at all your excel file, where this "BIN" came from ?

    Your solution is not correct for my need. Check this pic
    (remove the space at png : https://i.ibb.co/tD36p00/pbi.pn g), this was already in the topic.
    I don't need to get the duration from a false then a true but from all false between 2 true (check red line). So you won't get 1080s but 1886s from this exemple.
    And i don't need the duration between true state, because i won't summarize it if i can join the "GSM"


    • Edited by CalloudB Monday, January 27, 2020 10:37 AM
    Monday, January 27, 2020 10:34 AM
  • So instead of getting the Max of the datetime column don't you just want to get the MIN, but add a filter for joinable = false?

    eg.

    Durée 2 =
    IF (
    'JournalPing'[Joignabilité] = TRUE,
    DATEDIFF (
    CALCULATE (
    MIN ( 'JournalPing'[App_DateTime] ),
    FILTER (
    ALLEXCEPT ( 'JournalPing', 'JournalPing'[Site] ),
    'JournalPing'[App_DateTime] < EARLIER ( 'JournalPing'[App_DateTime] )
    && 'JournalPing'[Joignabilité] = FALSE
    )
    ),
    'JournalPing'[App_DateTime],
    SECOND
    ),
    0
    )


    http://darren.gosbell.com - please mark correct answers

    Monday, January 27, 2020 11:50 PM
    Moderator
  • Added a 4th PP version that uses DAX Measures.
    All data and algorithms are of my invention and illustrate Excel tools.
    I don't advise using the 4th method due to it's complexity.
    Updated links.

    Tuesday, January 28, 2020 3:46 AM
  • @Darren

    This method will get the datediff between the first date with a false and do the difference at each line.
    So for September 2019 i get around 5M seconds.
    Tuesday, January 28, 2020 7:56 AM
  • @Darren

    This method will get the datediff between the first date with a false and do the difference at each line.
    So for September 2019 i get around 5M seconds.

    Sorry I forgot to add a check for the first false after a true. The following expression should fix this. I've broken this one down a bit more using variables as it's getting a bit longer now and the variables help with readability.

    Durée 2 =
    VAR _thisDate = 'JournalPing'[App_DateTime]
    VAR _nextTrue =
        CALCULATE (
            MIN ( 'JournalPing'[App_DateTime] ),
            FILTER (
                ALLEXCEPT ( 'JournalPing', 'JournalPing'[Site] ),
                'JournalPing'[App_DateTime] < _thisDate
                    && 'JournalPing'[Joignabilité] = TRUE
            )
        )
    VAR _earliestFalse =
        CALCULATE (
            MIN ( 'JournalPing'[App_DateTime] ),
            FILTER (
                ALLEXCEPT ( 'JournalPing', 'JournalPing'[Site] ),
                'JournalPing'[App_DateTime] < _thisDate
                    && 'JournalPing'[App_DateTime] > _nextTrue
                    && 'JournalPing'[Joignabilité] = FALSE
            )
        )
    VAR _result =
        IF (
            'JournalPing'[Joignabilité] = TRUE,
            DATEDIFF ( _earliestFalse, _thisDate, SECOND ),
            0
        )
    RETURN
        _result


    http://darren.gosbell.com - please mark correct answers

    Tuesday, January 28, 2020 11:45 AM
    Moderator
  • Sorry i've been a bit long.

    Well it still not that.
    This formula get again the first "App_DateTime". So its returning me like 3M seconds from the last date i got and get closer to 0 second when i'm approaching the first date.
    I play a bit with it, but nothing as changed.

    Maybe i can share you an Excel file ?

    Edit :

    Excel file : https://send.firefox.com/download/67304104c61011bc/#-8Ba1cSAEGvvwQo3m4XEkA
    will expire after 5 DL or 7Days

    • Edited by CalloudB Thursday, January 30, 2020 9:13 AM Edit
    Thursday, January 30, 2020 9:00 AM
  • Thanks, the sample data file helped (it's hard to write DAX against a screenshot). I just had to change the calculation for the _nextTrue to use a MAX instead of MIN to fix the issue. But changing the RETURN statement to return each of the variables I could see that the _nextDate was not calculating what it should have been.

    Durée 2 =
    VAR _thisDate = 'JournalPing'[App DateTime]
    VAR _nextTrue =
    CALCULATE (
    MAX ( 'JournalPing'[App DateTime] ),
    FILTER (
    ALLEXCEPT ( 'JournalPing', 'JournalPing'[Site] ),
    'JournalPing'[App DateTime] < _thisDate
    && 'JournalPing'[Joignability] = TRUE
    )
    )
    VAR _earliestFalse =
    CALCULATE (
    MIN ( 'JournalPing'[App DateTime] ),
    FILTER (
    ALLEXCEPT ( 'JournalPing', 'JournalPing'[Site] ),
    'JournalPing'[App DateTime] < _thisDate
    && 'JournalPing'[App DateTime] > _nextTrue
    && 'JournalPing'[Joignability] = False
    )
    )
    VAR _result =
    IF (
    'JournalPing'[Joignability] = True,
    DATEDIFF ( _earliestFalse, _thisDate, SECOND ),
    0
    )
    RETURN
    _result


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by CalloudB Thursday, January 30, 2020 12:58 PM
    Thursday, January 30, 2020 11:46 AM
    Moderator
  • Yes it's always better with some datas.

    Wow, nice ! :D

    Clearly what i need.
    This was really a difficulty i have trouble to pass through.

    I'm not really in habit with VAR.
    Do you have any advise, or tips, to know when a VAR is needed ?

    Really a huge thanks, i can go further now with my report.
    Thursday, January 30, 2020 3:20 PM