DateDiff with a BLANK value

[RvdHeijden]

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 sometimes the second column hasn't got a value yet.

So i need to change this formula so that WHEN 'Klachten [Gefiatteerd]  ISBLANK it uses TODAY for a date.

Does anyone know how i can do that ?

Doorlooptijd = SWITCH ( TRUE ();
    Klachten[binnengekomen] < Klachten[gefiatteerd]; DATEDIFF ( Klachten[binnengekomen]; Klachten[gefiatteerd]; DAY );
    Klachten[gefiatteerd] > Klachten[binnengekomen]; DATEDIFF ( Klachten[gefiatteerd]; Klachten[binnengekomen]; DAY ) * -1; 0 )

[vanessafvg]

@RvdHeijden or you could say

test column =
IF ( startdate < enddate, DATEDIFF ( startdate, enddate, DAY ), BLANK () )

or

test column =
IF ( ISBLANK ( enddate ), BLANK (), DATEDIFF ( startdate, enddate, DAY ) )

[RvdHeijden]

@vanessafvg

if i understand your formula it doesn't return a value if the enddate is BLANK right ?

My question was that IF the enddate is BLANK it should use TODAY to fill in the blank Enddate

[vanessafvg]

@RvdHeijden

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

yep sorry missed that

[RvdHeijden]

@vanessafvg

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

[vanessafvg]

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

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

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

@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 ()
)

[vanessafvg]

@RvdHeijden why dates between, datediff calculates the days between 2 dates, isn't that what you wanted?  that what you posted earlier.  Dates between calculates a measure between a period

the isworkday columns is that an  number or a text field?

datediff  https://msdn.microsoft.com/en-gb/library/dn802538.aspx

datesbetween  https://msdn.microsoft.com/en-us/library/ee634557.aspx

please post pics of your data and relationships if you want more help. or your pbix.  i am working a bit blind here.

[RvdHeijden]

@vanessafvg

My table is very simple, its about damages and complaints.

A complaint is entered in the system on 25-7-17  and is closed on 28-07-17 so that complaint took 4 working days to resolve.

If a complaint is entered on 25-7-17 and is closed on 01-08-17 it took 8 days but there is a weekend in it so it's actually 6 working days. In my other rapports i have a table 'Date' with a calendar function, i thought the formula should check in this table how many workingdays (column 'IsWorkday'=1) there are between Start and End.

But if you say there is a better way i'm all ears.

the Relationship between Damages and Complaints and 'Date' is obviously on the 'date', direction both ways.

[vanessafvg]

@RvdHeijden hi ok so at this point what is wrong with the current calculation, that it doesn't take the isworkday = 1 into consideration.

did you check the data type ie if isworkday isnt a text field you will need to change it to isworkday = "1"

its  hard for me to understand what the problem is without having a look at your actual data.

post screen shots of the data, with the measure and whats its returns. show all the columns required otherwise its not going to be easy to resolve what is going on

[RvdHeijden]

@vanessafvg

im not sure what more information you need, i thought i made it clear what the question was.

But hopefully this will help you out.

the column 'IsWorkday' is set to 'Whole number'

i have a start and an enddate, i need to calculate the number of workingdays in between both dates

 

[vanessafvg]

@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

[RvdHeijden]

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

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

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

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

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

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