Forum Discussion

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

Post Prodigy

9 years ago

That seems to work better but i just have 2 more questions

1. If the 'begindate' = 'enddate' it returns 0 but it should return 1, can i just +1 in the formula ?

2. i want it to calculate using workdays (sorry i didn't mention it the first time)

i have a 'Date' table and a colum 'IsWorkday' with value 0 and 1

Community Champion

9 years ago

RvdHeijden you could also do a switch ofr this could work

test =
IF (
    IsWorkday = 1,
    IF (
        startdate = enddate,
        1,
        IF (
            ISBLANK ( enddate ),
            DATEDIFF ( startdate, TODAY (), DAY ),
            DATEDIFF ( startdate, enddate, DAY )
        )
    ),
    BLANK ()
)

RvdHeijden
Post Prodigy
9 years ago
vanessafvg
That wont work i think, the 'IsWorkday' is in the table 'Date' en the rest is in the table 'Klachten'
i have a relationship between both tables on 'Date' ofcourse but i think your formula needs to be changed
- vanessafvg
  Community Champion
  9 years ago
  RvdHeijden try this
  
  test =
  IF (
      RELATED ( Calendar[IsWorkday] ) = 1,
      IF (
          startdate = enddate,
          1,
          IF (
              ISBLANK ( enddate ),
              DATEDIFF ( startdate, TODAY (), DAY ),
              DATEDIFF ( startdate, enddate, DAY )
          )
      ),
      BLANK ()
  )
  - RvdHeijden
    Post Prodigy
    9 years ago
    vanessafvg
    The formula doesn't return an error but it also does not return any values :)
    You use datediff but shouldn't it be datesbetween ? it has to look in the date table to count the number of days between begin and end and then count the values 1 right ?
    
    Doorlooptijd =
    IF (
        RELATED ( 'Date'[IsWorkday] ) = 1;
        IF (
            schades[binnengekomen] = Schades[gefiatteerd];
            1;
            IF (
                ISBLANK ( Schades[gefiatteerd] );
                DATEDIFF ( schades[binnengekomen]; TODAY (); DAY );
                DATEDIFF ( schades[binnengekomen]; Schades[gefiatteerd]; DAY )
            )
        );
        BLANK ()
    )

Related content