Calculate working days between order and delivery date

[Johanno]

Hi, I'm going nuts. I want to calculate the number of workings days between order date and delivery date for each order. 

I have created a column for defining which dates are workings days and holidays:

So far so good. I have also set the dates to dates and tried creating relationship between order and delivery date to my date table:

I notice that the lines between delivery date and the date table is dotted - why?

I can easily subtract the delivery date from order date to get the results with holidays:

Leveransdagar = DATEDIFF(Underlaget[Orderdatum];Underlaget[Lev. datum];DAY)

I have searched everywhere to just sum up my working days and exlude holidays. I would prefer creating a measure but a column will do if I just could get it to work.

I would like to do something like this, but Power BI doesn't want me to do it:

Is there anybody out there who could help me? 

[Anonymous · Accepted solution]

If I understand it correct you have a date table that lists if a date is a working day. It is 1 for working dates and 0 for weekends/holidays. 

Firstly the doted line is a disabled relationship as you cannot have two relationships between the same tables. You have to either use a different solution or duplicate the table.

For your main query. Given you did not provide the data structure of your fact table I will assume it is structurd as follows

OrderNumber  OrderDate  DeliveryDate

1                       14/1/2018    17/1/2018

2                       15/2/2018    20/2/2018

3                        21/3/2018   29/2/2018

Column = CALCULATE ( SUM ( DateTable[Workday] ),
           DATESBETWEEN ( DateTable[Date], 
                        Table1[OrderDate],
                        Table1[DeliveryDate] )
          )

What this column is doing is summing the number of workdays in between the two dates.

Hopefully this answers your questions

[Johanno]

I think you nailed it.  You understood me correctly. My problem was my calendar didn't cover all my order dates, and I had to add -1 to your formula to get the correct days as I want to see them. But the main thing is that I guess I have to learn more about when using calculated columns instead of measures since I don't understand why your code doesn't work as a measure:

Anyway - thank you for your help! 

If anyone know how to write the corresponding calculation with a measure I would very much like to see it.

[Anonymous]

Measure =
SUMX (
    Table1,
    CALCULATE (
        SUM ( DateTable[Workday] ),
        DATESBETWEEN ( DateTable[Date], Table1[OrderDate], Table1[DeliveryDate] )
    )
)

This should work.

The reason I first did it as a calculated column is because that is the first solution that I thought of. The measure should be better for performance etc.

[Johanno]

You are great! Now I have a perfect match between my calculated column and my measure:

I notice that the error in my first message was that I tried to use SUM instead of SUMX and that I referred to the Date table instead of the fact table. In Sweden we say SBS = Skit bakom spakarna = s h i t in front of the keyboard

/Johan