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

 

 

 

  • 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

  • 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