DateDiff with a BLANK value

vanessafvg

The start and enddate of a complaint is in the same row and in the same table

Complaint          Startdate             Enddate                     difference (in workingdays)

A                        25-7-17               27-7-17                       3

B                        25-7-17               1-8-17                         6

RvdHeijden

i am a visual person, so when you put visuals in front of me i see the problem much quicker. 

i think the issue is your relationship via the date table.  effectively you need to merge your schades and klacten tables, the date is not enough to resolve the relalionship between the 2, you could have multiple complaint and damages on the same day, how does it know to link to which one?  If not merge the two tables  then you need to link the two tables together.

is id in both the tables the same?  ie. how do you know which complaint belongs to which damages (as i said date is not enough to resolve  the relationship between these 2 tables) - unless you just wanting to aggregate at a date level counts etc.

effectively you either need to merge  the two tables, which you can do in powerquery  (m) on id (if that is the same)

or you need to do a summarizetable in dax.  Or if there is a one to one relationship between one complaint and one damage you can just create a relationship, or you need to create a bridge table if there are many to many scenarios) (ie a unique list of id's which you link the tables)

ie there is a variety of methods to resolve this issue, it just really depends on your underlying data scenarios as to which is easiest

vanessafvg

It looks like my explenation made it harder for you :)

the Table 'Damage' en the Table 'Complaints' have NO relationships between eachother and there is no need to. The only relationship they both have is with the table 'Date'.

Fore example:

A complaint is registered in our system on the 25th of July and is solved on the 27th of July, that means it took us 3 days (Tuesday till Thursday) to resolve that complain, value should be 3 workingdays.

A complaint is registered in our system on the 25th of July and is solved on the 1st of August, that means it took us 8 days (Tuesday till Tuesday) to resolve that complain, but in those 8 days there is a Saterday and a Sunday, so the value should be 6 workingdays.

RvdHeijden

i get that, so are you saying there is only one complaint per day? and one damage per day?  because how does it know which date links a complaint to a damage?

vanessafvg
Why does that matter ?

we have a list in which we register all complaints, wether its 1 a day or 100 a day.

The formula should calculate per row what the time is it took us to resolve the complain without counting weekends

RvdHeijden look i am trying to explain a very basic data modelling concept to you

you can only calculate that if the complaint start date and end date is in the same row.

are they in the same row in the same table?

how will power bi know which row belongs to which because the link needs to be unique if there are hundreds of dates, it doesn't matter if there is a link to the tables on either side, its not unique

Hi RvdHeijden

Try with this calc column:

Difference in working Days =
VAR EndDate =
    IF ( Compliants[EndDate] = BLANK (), TODAY (), Compliants[EndDate] )
RETURN
    COUNTROWS (
        FILTER (
            CalendarTable,
            CalendarTable[IsWorkingDay] = 1
                && CalendarTable[Date] >= Compliants[StartDate]
                && CalendarTable[Date] <= EndDate
        )
    )

Let me knows if work

Regards

Victor

Lima - Peru