Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi All,
I have dataset like below, I want to show =Average order frequency.
Like, we are reciving the order for every X minutes.
I have tried different calculation as suggested by in the forum. But, couldn't get the expected result.
Solved! Go to Solution.
Hi @Adhavan ,
Here I suggest you to try this code to create a calculated column.
Diff column =
VAR _LASTORDER =
CALCULATE ( MIN ( 'Table'[order id] ), ALLEXCEPT ( 'Table', 'Table'[date] ) )
VAR _DIFF =
[Datetime]
- MAXX (
FILTER ( 'Table', 'Table'[order id] = EARLIER ( 'Table'[order id] ) - 1 ),
[Datetime]
)
RETURN
IF ( 'Table'[order id] = _LASTORDER, BLANK (), _DIFF )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Adhavan ,
Hi @Adhavan ,
@Adhavan , In case Order ID are incremental only
a new column
Datetime= [Date]+ [Time]
Diff column =
[Datetime] - maxx(filter(Table, [Order_id] = earlier([Order_id]) -1) ,[Datetime] )
a Measure
Average(Table[Diff column])
Its provided the result at each column. But, it calculate wrong average value due to following reason, which I couldn't elimate nor find the solution.
1. It calculates 18 hours for Jan' 1st alone.
2. It showing 12+ hours for every first order of the day (calculating from previous day)
I have attached the reference image. Could you help me to rid out of that.
Hi @Adhavan ,
Here I suggest you to try this code to create a calculated column.
Diff column =
VAR _LASTORDER =
CALCULATE ( MIN ( 'Table'[order id] ), ALLEXCEPT ( 'Table', 'Table'[date] ) )
VAR _DIFF =
[Datetime]
- MAXX (
FILTER ( 'Table', 'Table'[order id] = EARLIER ( 'Table'[order id] ) - 1 ),
[Datetime]
)
RETURN
IF ( 'Table'[order id] = _LASTORDER, BLANK (), _DIFF )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
97 | |
60 | |
47 | |
35 | |
34 |