Comparing dates in a table

[andrew_hardwick]

I have created a measure to create the difference between 2 dates in seperate tables. The first table holds the records that were created, the second table holds when the fields in table 1 were changed. I want to work out the difference between when the record was created and when it was changed. I have setup a relationship between the 2 tables based on the unique ID.

I have created a visualisation in Power BI that holds the Record ID, Open Date, Changed Date, Closed Date and Days to Next Value. Days to Next value should show the difference between either open date and changed date, or open and closed or open and now. The table is showing as below.

Record ID     Open Date                       Changed Date                  Closed Date                      DaysToNextValue

00001013     09/02/2017 14:07:26        09/02/2017 16:23:21                                                              1

00001019     14/02/2017 12:40:16        14/02/2017  12:45:21       02/03/2017 14:02:32                     1

00001037     21/02/2017 10:37:18        22/02/2017 13:05:11                                                              2

00001038     21/02/2017 11:45:46                                                 17/05/2017 11:54:04                    380 

00001335     26/04/2017 09:33:39                                                                                                      380

The first 3 records are displaying fine as the Changed Date is populate. However where the changed date is empty, I would like to use the Closed date to determine the value, so for line 4 I would expect to see the result 63. For the last line where changed date and closed date are empty, I would like to use the current date, so would expect to see 37.

The DAX I am using is-

MeasureDaysToNextValue = if(min('History: Problem'[CreatedDate])>0 && min('history: problem'[createddate])>0,CALCULATE(sum('Date'[IsWorkday]),DATESBETWEEN('Date'[Date],min(Problem[BMCServiceDesk__Open_Date_Time__c]),MIN('History: Problem'[CreatedDate]))),if(MIN('Problem'[BMCServiceDesk__Closed_Date_Time__c])>0,CALCULATE(sum('Date'[IsWorkday]),DATESBETWEEN('Date'[Date],min(Problem[BMCServiceDesk__Open_Date_Time__c]),MAX(Problem[BMCServiceDesk__Closed_Date_Time__c]))),CALCULATE(sum('Date'[IsWorkday]),DATESBETWEEN('Date'[Date],min(Problem[BMCServiceDesk__Open_Date_Time__c]),NOW()))))

Any suggestions? Thanks.

Andy

[v-sihou-msft]

@andrew_hardwick

In this scenario, you could use calculated column instead of  measure. It will be much easier to achieve you logic. Just apply IF statement like:

Column =
1
    * (
        IF (
            Table[Changed Date] <> BLANK (),
            Table[Changed Date],
            IF ( Table[Closed Date] <> BLANK (), Table[Closed Date], NOW () )
        )
            - Table[Open Date]
    )

Regards,

[andrew_hardwick]

Hi @v-sihou-msft

I have tried a Column previously but had issues getting the right values, which is why I tried the Measure. However I have followed your advice and am now getting an error stating-

A single value for column 'BMCServiceDesk__Closed_Date_Time__c' in table 'problem' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

The DAX is-

ColumnDaysToNextValue = 1
    * (
        IF (
            'History: Problem'[ChangedDate] <> BLANK (),
            'History: Problem'[ChangedDate],
            IF ('problem'[BMCServiceDesk__Closed_Date_Time__c] <> BLANK (), 'problem'[BMCServiceDesk__Closed_Date_Time__c], NOW () )
        )
            - 'problem'[BMCServiceDesk__Open_Date_Time__c]
    )