cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Resolver II

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.

Best Regards,
changqing

6 REPLIES 6
Resolver II

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.

Best Regards,
changqing

Frequent Visitor

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

Frequent Visitor

UP

Frequent Visitor

kindly help

Super User

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

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors