Help with calculating the number of days in between 2 date columns with a specific requirement

[Anonymous]

Hi, 

I have the below data set in a Pbix file. Basically what I need it to count the number of days an order was under a specific delivery block (33). On this dataset one order may go under many different blocks, but I only need to see the duration it was under block 33. Looking at the Data below, we can see the order was place on block 33 firstly on 03/14 and taken off 03/15, then placed on 33 again on 03/20 and taken 0ff on 03/22, then once again placed under 33 on 03/28 and taken off 04/01. That would total 7 days under block 33. Note that the old Value Field can be empty or it can have another value, however, we only want to see when the date the New Value changes to 33 and the date it changes from 33 into something else so we can calculate the number of days for that specific action. Any help would be deeply appreciated. Thank you.

[Anonymous · Accepted solution]

Hi @Anonymous 

For your question, here is the method I provided:

"Table"

First, you need to add an index column to the power query.

Creates a new column that looks up the date with alias 33.

Mark 33 DATE = 
IF(
         ('Table'[New Value] = 33 && ISBLANK('Table'[Old Value]))
        ||
        'Table'[Old Value] = 33
    ,
    'Table'[Update Date]
)

Create a column or a measure.

Column = 
var currentIndex = IF(
        'Table'[Mark 33 DATE] = 'Table'[Update Date]
        &&
        'Table'[Old Value] = 33
    ,
    'Table'[Index]
)
var previousDate = 
CALCULATE(
    MAX('Table'[Mark 33 DATE]), 
    FILTER(
        'Table', 
        'Table'[Index] < currentIndex
    )
)
var currentDate = 
CALCULATE(
    MAX('Table'[Mark 33 DATE]), 
    FILTER(
        'Table', 
        'Table'[Index] = currentIndex
    )
)
RETURN DATEDIFF(previousDate, currentDate, DAY)

OR

Measure = 
var currentIndex = 
IF(
    SELECTEDVALUE('Table'[Mark 33 DATE]) = SELECTEDVALUE('Table'[Update Date])
    &&
    SELECTEDVALUE('Table'[Old Value]) = 33,
    SELECTEDVALUE('Table'[Index])
)
var previousDate = 
CALCULATE(
    MAX('Table'[Mark 33 DATE]), 
    FILTER(
        ALL('Table'), 
        'Table'[Index] < currentIndex
    )
)
var currentDate = 
CALCULATE(
    MAX('Table'[Mark 33 DATE]), 
    FILTER(
        ALL('Table'), 
        'Table'[Index] = currentIndex
    )
)
RETURN DATEDIFF(previousDate, currentDate, DAY)

 Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous 

For your question, here is the method I provided:

"Table"

First, you need to add an index column to the power query.

Creates a new column that looks up the date with alias 33.

Mark 33 DATE = 
IF(
         ('Table'[New Value] = 33 && ISBLANK('Table'[Old Value]))
        ||
        'Table'[Old Value] = 33
    ,
    'Table'[Update Date]
)

Create a column or a measure.

Column = 
var currentIndex = IF(
        'Table'[Mark 33 DATE] = 'Table'[Update Date]
        &&
        'Table'[Old Value] = 33
    ,
    'Table'[Index]
)
var previousDate = 
CALCULATE(
    MAX('Table'[Mark 33 DATE]), 
    FILTER(
        'Table', 
        'Table'[Index] < currentIndex
    )
)
var currentDate = 
CALCULATE(
    MAX('Table'[Mark 33 DATE]), 
    FILTER(
        'Table', 
        'Table'[Index] = currentIndex
    )
)
RETURN DATEDIFF(previousDate, currentDate, DAY)

OR

Measure = 
var currentIndex = 
IF(
    SELECTEDVALUE('Table'[Mark 33 DATE]) = SELECTEDVALUE('Table'[Update Date])
    &&
    SELECTEDVALUE('Table'[Old Value]) = 33,
    SELECTEDVALUE('Table'[Index])
)
var previousDate = 
CALCULATE(
    MAX('Table'[Mark 33 DATE]), 
    FILTER(
        ALL('Table'), 
        'Table'[Index] < currentIndex
    )
)
var currentDate = 
CALCULATE(
    MAX('Table'[Mark 33 DATE]), 
    FILTER(
        ALL('Table'), 
        'Table'[Index] = currentIndex
    )
)
RETURN DATEDIFF(previousDate, currentDate, DAY)

 Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thank you very much Nuno. We mad a small adaptation here but it worked. I appreciate the help.