# Duration problem (gap)

• ### 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

• 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

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

### 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 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
)

Monday, January 27, 2020 11:50 PM
• 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.

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

Tuesday, January 28, 2020 11:45 AM
• 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 :

will expire after 5 DL or 7Days

• Edited by 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

• Marked as answer by Thursday, January 30, 2020 12:58 PM
Thursday, January 30, 2020 11:46 AM
• 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