Calculating the number of calendar days between two dates

[nbridwell]

I'm trying to calculate how many days it took our support team to escalate a support case.  Not all support cases are escalated so I will have blank fields.  I'm pulling the data from two different tables because the data is in two different systems. I'm not sure if I need to create a Columm or a Measure and where to put it.  I was able to associate the two tables and create a new column in the Support's System table that pulls in the R&D's System (Create Date). I just can't for the life of me figure out how to calculate the days without getting errors.  I don't want Network days. I care about calendar days.

Example:

Support's System (Create Date) - Always has a date

R&D's System (Create Date) - Does not always have a date

Any help is greatly appreciated.

[aktripathi2506]

To calculate number of days in between try this: 

Considering both the columns are in the same table if they are not in the same table then you can join/merge them and then try creating a new column with the formula.

DaysNo = IF(Table[Date1]<Table[Date2],DATEDIFF(Table[Date1],Table[Date2],DAY),1*DATEDIFF(Table[Date2],Table6[Date1],DAY))

[nbridwell]

I'm trying to calculate how many days it took our support team to escalate a support case.  Not all support cases are escalated so I will have blank fields.  I'm pulling the data from two different tables because the data is in two different systems. I'm not sure if I need to create a Columm or a Measure and where to put it.  I was able to associate the two tables and create a new column in the Support's System table that pulls in the R&D's System (Create Date). I just can't for the life of me figure out how to calculate the days without getting errors.  I don't want Network days. I care about calendar days.

Example:

Support's System (Create Date) - Always has a date

R&D's System (Create Date) - Does not always have a date

Any help is greatly appreciated.

[CheenuSing · Accepted solution]

Hi @nbridwell

I created a table as 

CaseNo	SupportDate	R&Ddate
1	10/06/2016	12/06/2016
2	20/06/2016
3	30/06/2016	01/08/2016

I set the Support Date and R&Ddate of type date format dd/mm/yyyy

To calculate the days between the two created a column 

DaysDifference = IF (isblank([R&Ddate]),blank(),1.*([R&Ddate]-[SupportDate]))

The highlighted portion is the way to calculate the difference in dates in DAX, a bit convuluted formula.

The logic deployed is if [R&Ddate] is blank, return blank () ( could be zero depending on your requirement) , else calculate the difference in days between [R&Ddate] & [SupportDate] .

If this solves your issue please accept it as solution and also give KUDOS.

Cheers

CheenuSing

[nbridwell]

I plugged in the formula as a New Column and it worked. Thank you so much.

Days to Escalate = IF ( ISBLANK ( 'NS Cases'[TFS Create Date] ), BLANK (), 1. * ( 'NS Cases'[TFS Create Date] - 'ns cases'[Date Created] ) )

Thank you so much. I'm just getting into Power BI, so I'm sure I'll have a ton more questions.

[nbridwell]

Thank you for your quick response. I plugged in the formula and I get an error stating:

Too few arguments were passed to the IF function. The minimum argument count for the function is 2.  

I can say that I haven't seen this one yet, so I feel like we are close.

[Sean]

@nbridwell looks like you may have a comma missing somewhere (because the formula looks fine)

make sure your formula has all " , " and opening " ( " and closing " ) " 

DaysDifference =
IF ( ISBLANK ( [R&Ddate] ), BLANK (), 1. * ( [R&Ddate] - [SupportDate] ) )

[CheenuSing]

Hi @nbridwell

Can you paste the code you had entered 

[nbridwell]

I am trying to do this through Modeling and adding a New Measure. Is that the right place? I changed my column names, but here is what I have written as the measure. This forumula doesnt' work for any of the dates in that table, so I know it's not isolated to just these two columns.

Days Difference = IF(isblank([TFS Create Date),blank(),1.*([TFS Create Date]-[Date Created]))