Forum Discussion

andrew_hardwick's avatar
andrew_hardwick
Helper III
9 years ago

Comparing dates in a table

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 chang...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

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's avatar
andrew_hardwick
Helper III
9 years ago

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