Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Shak_95
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 

 

Shak_95_0-1659557369390.png

 

Shak_95_1-1659557409350.png

Shak_95_3-1659557453658.png

Shak_95_4-1659557707189.png

 

Your answer is appreciated

 

1 ACCEPTED SOLUTION
changqing
Resolver II
Resolver II

Hi @Shak_95 ,

 

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

changqing_1-1660039833690.png

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

changqing_2-1660039948074.png

The PBIX file is attached for reference.

PBIX file 

 

Best Regards,
changqing

View solution in original post

6 REPLIES 6
changqing
Resolver II
Resolver II

Hi @Shak_95 ,

 

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

changqing_1-1660039833690.png

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

changqing_2-1660039948074.png

The PBIX file is attached for reference.

PBIX file 

 

Best Regards,
changqing

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

Shak_95
Frequent Visitor

UP

Shak_95
Frequent Visitor

kindly help

technolog
Super User
Super User

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

thank you for your reply

 

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.