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

[Shak_95]

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 invoice I need to apply this condition :
BEWTP=E and BWART=101 and WERKS=1000

- to get the date of payment I need to apply this condition
BEWTP=Q and SHKZG=S and WERKS=1000 

Your answer is appreciated

[changqing · Accepted solution]

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

[Shak_95]

Thank you very much changqing for your help, i appreciate it

[Shak_95]

UP

[Shak_95]

kindly help

[technolog]

I think that you can try to use SUMMARIZE and after that use ADDCOLUMNS with MAX(Date) - MIN(Date)

[Shak_95]

thank you for your reply

Kindly, can you share the expression and steps that achieves what i want.