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.
I'm new to power BI.Basically I need to find for #1) Average days between purchases and #2) Days since last purchase. From these 2 measure I can display days between most recent purchases for #1 and average days between purchase for #2 for selected grouping.I hope I have explained my question in a proper way in the below table.
SalesDate | InvoiceNo | Customer Account | InvoiceAmt |
7/20/2023 | 50465123 | 2881122 | 100 |
12/28/2022 | 43435546 | 5553332 | 400 |
9/17/2023 | 33332211 | 7744333 | 488.8 |
1/1/2019 | 76555544 | 1021223 | 90.2 |
10/26/2022 | 33322221 | 2881122 | 5 |
11/17/2023 | 33332231 | 7744333 | 42.2 |
3/1/2019 | 76555542 | 1021223 | 30 |
4/20/2023 | 90465123 | 2881122 | 120 |
So,I need a dax query which can calculate for 2 items above.Thank you for your help!
Try
Average Days Between Purchases =
AVERAGE(
DATEDIFF(
MIN(SalesDate),
CALCULATE(
MIN(SalesDate),
FILTER(
ALL(SalesDate),
SalesDate > MIN(SalesDate)
)
),
DAY
)
)
And
Days Since Last Purchase =
DATEDIFF(
MAX(SalesDate),
TODAY(),
DAY
)
I had tried for Average Days Between Purchases but it not works.However for Days Since Last Purchase is works fine.Thanks
Hi @Kella
You can refer to the following ways to calculate average
create two measures
Measure = DATEDIFF(MIN([SalesDate]),CALCULATE(MIN([SalesDate]),FILTER(ALLSELECTED('Table'),'Table'[SalesDate]>MIN([SalesDate])&&[ Customer Account]=MAX([ Customer Account]))),DAY)
Measure 2 = DIVIDE(SUMX(FILTER(ALLSELECTED('Table'),[ Customer Account]=MAX([ Customer Account])),[Measure]),COUNTROWS(FILTER(ALLSELECTED('Table'),[ Customer Account]=MAX([ Customer Account])&&[Measure]<>BLANK())))
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @Kella
for averge, try like:
Average Days Between Purchases =
DIVID(
MAX(TableName[SalesDate]) - MINX(TableName[SalesDate]),
COUNTROWS(VALUES(TableName[SalesDate]))-1
)
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |