topic Re: DAX to calculate difference between two dates in same column with conditions ! in DAX Commands and Tips

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

Shak_95 — Wed, 03 Aug 2022 20:15:24 GMT

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

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

technolog — Thu, 04 Aug 2022 01:28:40 GMT

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

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

Shak_95 — Thu, 04 Aug 2022 06:21:18 GMT

thank you for your reply

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

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

Shak_95 — Fri, 05 Aug 2022 13:00:46 GMT

kindly help

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

Shak_95 — Sat, 06 Aug 2022 14:51:28 GMT

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

changqing — Tue, 09 Aug 2022 10:15:36 GMT

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

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

Shak_95 — Tue, 09 Aug 2022 15:25:50 GMT

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