Forum Discussion

RvdHeijden's avatar
RvdHeijden
Post Prodigy
9 years ago

DateDiff with a BLANK value

I have a question about a 'simple' datediff formula.   Ive got 2 date values and i need to know the difference between them. At first this is a simple question if both colums have values. But some...
Need Help
RvdHeijden's avatar
RvdHeijden
Post Prodigy
9 years ago

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.

 

vanessafvg's avatar
vanessafvg
Community Champion
9 years ago

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?

  • RvdHeijden's avatar
    RvdHeijden
    Post Prodigy
    9 years ago

    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's avatar
    RvdHeijden
    Post Prodigy
    9 years ago

    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

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    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

     

     

     

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    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