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