Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone!
A colleague would like to have an overview that shows the rythm in which the customer always orders in order to anticipate the next order.
We have a table that contains the customer number, the order number and the order date.
The problem for me is how Power BI can calculate the datediff of the orderdate between row 1 and 2 then between row 2 and 3 and so on. And then calculate an average from the calculated datediffs.
Thank you very much
Solved! Go to Solution.
Hi @Marcus_S ,
My table is :
You can follow the below steps:
1.Add a new column in the table to calculate the datediff
Date_Diff =
VAR curdate = 'Table'[Order date]
VAR prevoius_date =
CALCULATE (
MAX ( 'Table'[Order date] ),
FILTER ( 'Table', 'Table'[Order date] < curdate )
)
RETURN
DATEDIFF ( prevoius_date, curdate, DAY )
2.Create a measure to calculate the average about the new added column
Measure =
AVERAGE ( 'Table'[Date_Diff] )
If I misunderstand your demands, please feel free to let me know.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Marcus_S ,
My table is :
You can follow the below steps:
1.Add a new column in the table to calculate the datediff
Date_Diff =
VAR curdate = 'Table'[Order date]
VAR prevoius_date =
CALCULATE (
MAX ( 'Table'[Order date] ),
FILTER ( 'Table', 'Table'[Order date] < curdate )
)
RETURN
DATEDIFF ( prevoius_date, curdate, DAY )
2.Create a measure to calculate the average about the new added column
Measure =
AVERAGE ( 'Table'[Date_Diff] )
If I misunderstand your demands, please feel free to let me know.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your data model.
It is for creating a measure.
Rythm average measure: =
VAR _currentcustomerid =
MAX ( Customer[Customer ID] )
VAR _newtable =
ADDCOLUMNS (
FILTER (
ADDCOLUMNS (
FILTER ( ALL ( Data ), Data[Customer ID] = _currentcustomerid ),
"@previousorderdate",
MAXX (
FILTER (
Data,
Data[Customer ID] = EARLIER ( Data[Customer ID] )
&& Data[Order Date] < EARLIER ( Data[Order Date] )
),
Data[Order Date]
)
),
[@previousorderdate] <> BLANK ()
),
"@datediff", INT ( Data[Order Date] - [@previousorderdate] )
)
RETURN
IF (
HASONEVALUE ( Customer[Customer ID] ),
AVERAGEX ( _newtable, [@datediff] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Marcus_S , Try a new column like
New column = datediff( maxx(filter(Table, [Customer number] = earlier([Customer number]) && [Order Date] = earlier([Order date])),[Order date]),[Order Date] ,day)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
82 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |