Forum Discussion

Shak_95's avatar
Shak_95
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 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's avatar
    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

6 Replies

  • changqing's avatar
    changqing
    Icon for Resolver II rankResolver 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

    • Shak_95's avatar
      Shak_95
      Frequent Visitor
      4 years ago

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

  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    4 years ago

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

    • Shak_95's avatar
      Shak_95
      Frequent Visitor
      4 years ago

      thank you for your reply

       

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