Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.
Solved! Go to Solution.
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
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
You should look into similar thread with accepted solution.
https://community.powerbi.com/t5/Desktop/Date-difference-between-values-in-same-column/m-p/454940