How to calculate duration by dates

[Anonymous]

Hi,

Can you help me? 🙂

My data in the "Procurement" table:

Title	Scheduled end date	End date
PC	2020-06-02	2020-08-03
Monitor	2020-07-03	2020-07-03
PS4	2020-08-28

The following must be calculated:

- Check that the end date is entered. If yes - calculate whether there is a delay. If not - take a date today and calculate if there is a delay. If there is a delay - show in new field 1, if not - BLANK (). 

- Show in next field: if there is a delay - show how many working days are delayed. Calculate Networkdays by estimating all holidays. Holidays are stored in my another table.

In this case, the result should look like this:

Title	Scheduled end date	End date	Delay	Days
PC	2020-06-02	2020-08-03	1	43*
Monitor	2020-07-03	2020-07-03
PS4	2020-08-28		1	7**

*43, because 2020-06-24, 2020-07-06 days are holidays.

**7, because today is 2020-09-07. There will be a day more every day: tomorrow - 8, etc.

[v-stephen-msft]

Hi @Anonymous ,

1.Create a calendar table. There is no relationship between two tables.

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 6, 1 ), DATE ( 2020, 9, 30 ) ),
    "Holiday",
        IF (
            WEEKDAY ( [Date], 2 ) = 6
                || WEEKDAY ( [Date], 2 ) = 7
                || [Date] = DATE ( 2020, 6, 24 )
                || DATE ( 2020, 7, 6 ) = [Date],
            "Yes",
            "No"
        )
)

2.Create measures.

Delay =
IF (
    MAX ( 'Procurement'[End date] ) = MAX ( 'Procurement'[Scheduled end date] ),
    BLANK (),
    1
)

Days =
VAR t1 =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            [Date] <= MAX ( 'Procurement'[End date] )
                && [Date] >= MAX ( 'Procurement'[Scheduled end date] )
                && [Holiday] = "No"
        )
    )
VAR t2 =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            [Date] <= TODAY ()
                && [Date] >= MAX ( 'Procurement'[Scheduled end date] )
                && [Holiday] = "No"
        )
    )
RETURN
    SWITCH (
        TRUE (),
        [Delay] = BLANK (), BLANK (),
        [Delay] = 1
            && MAX ( 'Procurement'[End date] ) <> BLANK (), t1,
        [Delay] = 1
            && MAX ( 'Procurement'[End date] ) = BLANK (), t2
    )

3.The result is as follows.

You can check more details from here.

Best Regards,

Stephen Tao

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

[Anonymous]

@v-stephen-msft , thanks, but not everything is clear to me.
I will explain what I have now calculated and what I need.

I have already created two tables: Procurement and Holidays. I have also calculated the delay using measure.

Procurement table:

Title	Scheduled end date	End date
PC	2020-06-02	2020-08-03
Monitor	2020-07-03	2020-07-03
PS4	2020-08-28

Holidays table:

Date	Value
2020-01-01	1
2020-03-01	1
2020-04-13	1
etc...

Delay (I don't know if it's correct, or no, because I'm using two logic in this formula):

Delay = IF(('Procurement'[Scheduled end date])>=('Procurement'[End date]) || ('Procurement'[Procurement are made on schedule] ) = 1;BLANK();1)

I need to calculate:

- if I don't have an end date - how can I calculate the delay until today?

- and how can I calculate days? I need to show how many working days are delayed? I calculated Networkdays in another table. It may be possible to make a similar calculation: 

NetworkDays =
Var TBL_Date=CALENDAR(IF('Application card'[Start date] = BLANK();'Application card'[Creation date];'Application card'[Start date]);'Application card'[Registration date])
var TBL_FinalDate=ADDCOLUMNS(TBL_Date;"Working day";IF(WEEKDAY([Date];2)>=6;0;1);"Holiday";IFERROR(LOOKUPVALUE('Holidays'[Value];'Holidays'[Date];[Date]);0))
return
SUMX(TBL_FinalDate;if([Working day]=1&&[Holiday]=0;1;0))

[v-stephen-msft]

Hi @Anonymous ,

You have given a special holiday table. You can try to use the RELATED function to reference the columns of the holiday table in your calendar table, provided that the two tables have a relationship.

1.Calendar table. And there is a relationship between Calendar table and Holidays table.

Calendar = CALENDAR ( MIN('Procurement'[Scheduled end date]),TODAY() )

2.Create calculated columns in Calendar table. The Weekday column returns a number from 1 to 7,week begins on Monday (1) and ends on Sunday (7). The Weekend/Holiday column returns 1 if the date is a weekend or holiday, otherwise it returns 0.

Holiday = RELATED(Holidays[Date])

Weekday = WEEKDAY([Date],2)

Weekend/Holiday = IF([Weekday]=6||[Weekday]=7||[Holiday]=[Date],1,0)

3.According to your explanation, I modified the Delay measure. Since I don’t have a [Procurement are made on schedule] column, I use the End Date column as the judgment condition.

Delay =
IF (
    MAX ( 'Procurement'[Scheduled end date] ) < MAX ( 'Procurement'[End date] )
        || MAX ( 'Procurement'[End date] ) = BLANK (),
    1,
    BLANK ()
)

4.t1 is the number of days from the scheduled end date to the end date and excluding weekends and holidays.

   t2 is the number of days from the scheduled end date to today and excluding weekends and holidays.

   Then call t1 or t2 according to the return value of the Delay measure and whether the End date column is empty as the judgment condition.

Days =
VAR t1 =
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        FILTER (
            'Calendar',
            [Date] <= MAX ( 'Procurement'[End date] )
                && [Date] >= MAX ( 'Procurement'[Scheduled end date] )
                && [Weekend/Holiday] = 0
        )
    )
VAR t2 =
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        FILTER (
            'Calendar',
            [Date] <= TODAY ()
                && [Date] >= MAX ( 'Procurement'[Scheduled end date] )
                && [Weekend/Holiday] = 0
        )
    )
RETURN
    SWITCH (
        TRUE (),
        [Delay] = BLANK (), BLANK (),
        [Delay] = 1
            && MAX ( 'Procurement'[End date] ) <> BLANK (), t1,
        [Delay] = 1
            && MAX ( 'Procurement'[End date] ) = BLANK (), t2
    )

 

You can check more details from here.

Best Regards,
Stephen Tao

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

[Anonymous]

@v-stephen-msft , thank you. Everything looks good.

I want to ask a few more questions:

The calculation of the delay must include the logic (exception) that I apply to this day, if the column "Procurement is scheduled" is 1, do not apply the delay. For example:

Title	Scheduled end date	End date	Delay	Days	Procurement are made on schedule
PC	2020-06-02	2020-08-03	1	43
Monitor	2020-07-03	2020-07-03
PS4	2020-08-28		1	7
TV	2020-07-27	2020-08-03		6	1

And one more question:

In the table, except for that logic at the top, everything counts correctly. However, when I try to compare delays procurement with ongoing procurement or show how much is delay and how many are not delay, it doesn’t count.

Also in the Procurement table I see the scheduled end date of the procurement in the future and I see that they are already delay, even though those dates have not yet come, e.g. Scheduled end date: 2020-09-14

[Greg_Deckler]

@Anonymous - I didn't quite get all of that, but you can use Net Work Days to calculate the number of days between scheduled end and end. https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

[amitchandak]

@Anonymous ,

check this -https://www.sqlbi.com/articles/counting-working-days-in-dax/

Check my file page 2 - it has both date diff and business work days

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[Anonymous]

@amitchandak, I can't open your file, because my PBI Desktop version is older. 

Maybe it's possible to present the content in this file differently, for example in pics? Thank you.