Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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

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.

 

MarcioB_0-1719250511828.png

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @Anonymous 

 

For your question, here is the method I provided:

 

"Table"

 

vnuocmsft_0-1719296167286.png

 

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

 

vnuocmsft_1-1719296292129.png

 

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

 

vnuocmsft_2-1719296371381.png

 

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.

 

vnuocmsft_3-1719296635676.png

 

Regards,

Nono Chen

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

View solution in original post

Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @Anonymous 

 

For your question, here is the method I provided:

 

"Table"

 

vnuocmsft_0-1719296167286.png

 

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

 

vnuocmsft_1-1719296292129.png

 

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

 

vnuocmsft_2-1719296371381.png

 

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.

 

vnuocmsft_3-1719296635676.png

 

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
Not applicable

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.