Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Power Bi time differences

Hello I have a question, I have a database with different materials that are purchased on different dates, these are in the database, and I need to calculate how often this material is being purchased, that is, for example, a material x leaves date of purchase January 1, then January 6, then January 16, So between the first 2 there are 5 days and among the others there are 10 days then the average would be 7.5 days, I need to do that for each material, the dates are all in the same column.

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Syndicate_Admin ,

     

    Suppose we have a table:

    Please try this:

    Avg = 
VAR _hours =
    SUMX (
        SUMMARIZE (
            'Table',
            'Table'[Material],
            'Table'[Date],
            "HOUR",
                DATEDIFF (
                    'Table'[Date],
                    CALCULATE (
                        MIN ( 'Table'[Date] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Date] > EARLIER ( 'Table'[Date] )
                                && 'Table'[Material] = EARLIER ( 'Table'[Material] )
                        )
                    ),
                    DAY
                )
        ),
        [HOUR]
    )
VAR _times =
    COUNTROWS ( 'Table' ) - 1
VAR _result =
    DIVIDE(_hours,_times)
RETURN
    _result

    result:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 Replies