Forum Discussion

Frequent Visitor

4 years ago

Solved

DAX to calculate difference between two dates in same column with conditions !

Hi All I need to calculate the difference between the date of receipt of the invoice and the date of payment. Challenge: The two dates are in one column, - to get the date of receipt of the i...

changqing

4 years ago

Hi Shak_95 ,

Suppose there is a table like this(There should be an invoice id column):

Then new a calculated column:

days = 
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[CPUDT_DATE] ),
        FILTER (
            'Table',
            'Table'[BEWTP] = "E"
                && 'Table'[BWART] = 101
                && 'Table'[WERKS] = 1000
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[CPUDT_DATE] ),
        FILTER (
            'Table',
            'Table'[BEWTP] = "Q"
                && 'Table'[SHKZG] = "S"
                && 'Table'[WERKS] = 1000
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    )
VAR _difference =
    DATEDIFF ( _mindate, _maxdate, DAY )
RETURN
    _difference

The PBIX file is attached for reference.

PBIX file

Best Regards,
changqing

changqing

Resolver II

4 years ago

Hi Shak_95 ,

Suppose there is a table like this(There should be an invoice id column):

Then new a calculated column:

days = 
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[CPUDT_DATE] ),
        FILTER (
            'Table',
            'Table'[BEWTP] = "E"
                && 'Table'[BWART] = 101
                && 'Table'[WERKS] = 1000
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[CPUDT_DATE] ),
        FILTER (
            'Table',
            'Table'[BEWTP] = "Q"
                && 'Table'[SHKZG] = "S"
                && 'Table'[WERKS] = 1000
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    )
VAR _difference =
    DATEDIFF ( _mindate, _maxdate, DAY )
RETURN
    _difference

The PBIX file is attached for reference.

PBIX file

Best Regards,
changqing