Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Syndicate_Admin
Administrator
Administrator

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.

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @Syndicate_Admin ,

 

Suppose we have a table:

vcgaomsft_0-1674191080113.png

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:

vcgaomsft_1-1674191133695.png

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

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

Hi @Syndicate_Admin ,

 

Suppose we have a table:

vcgaomsft_0-1674191080113.png

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:

vcgaomsft_1-1674191133695.png

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

PowerBIEnthu
Resolver I
Resolver I

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors