Forum Discussion
Repurchase rate
I am trying to create a report that graphically shows per the given time period in the graph, the repurchase rate, number of orders and the number of orders where there were a repurchase.
I have the data:
- OrdNo as unique identifier for the orders
- CustNo as identifier for the customer on the order
- Date column
- +Columns to filter on
I need:
- Calculated column/Measure with the number of orders
- Calculated column/Measure with the number of orders that has a reccuring order in the last 12 month
- Calculated column/Measure with repurchase rate
I tried:
- Calculated column: # Purchases
# Purchases =
CALCULATE(
COUNTROWS('NOR Orders'),
FILTER(
'NOR Orders',
'NOR Orders'[Gr] < 3
),
FILTER(
'NOR Orders',
'NOR Orders'[CustNo] > 0
),
FILTER(
'NOR Orders',
'NOR Orders'[OrdDt] > 0
),
FILTER(
'NOR Orders',
'NOR Orders'[OrdTp] = 1
),
FILTER(
'NOR Orders',
'NOR Orders'[TrTp] < 4
),
FILTER(
'NOR Orders',
'NOR Orders'[CIncSF] > 0
)
)
- Calculated column: # Repurchases 12 month
# Repurchases 12 month =
CALCULATE(
COUNTROWS('NOR Orders'),
DATESINPERIOD(
'NOR Orders'[Date],
'NOR Orders'[Date]-YEAR(1),
1,
YEAR
),
FILTER(
'NOR Orders',
'NOR Orders'[Gr] < 3
),
FILTER(
'NOR Orders',
'NOR Orders'[CustNo] > 0
),
FILTER(
'NOR Orders',
'NOR Orders'[OrdDt] > 0
),
FILTER(
'NOR Orders',
'NOR Orders'[OrdTp] = 1
),
FILTER(
'NOR Orders',
'NOR Orders'[TrTp] < 4
),
FILTER(
'NOR Orders',
'NOR Orders'[CIncSF] > 0
),
FILTER(
'NOR Orders',
EARLIER('NOR Orders'[CustNo])='NOR Orders'[CustNo]
)
)
- Calculated column: # Repurchase rate 12 month
# Repurchase rate 12 month =
DIVIDE(
'NOR Orders'[# Repurchases 12 month],
'NOR Orders'[# Purchases]
)
I cannot seem to get it right, as for my numbers it simply does not add up.
I looked at Divide as Pivot Table in the forum as it seemed like a viable solution, but it did not work for me.
Any other tips and trix?
7 Replies
- AnonymousNot applicable
HI Anonymous,
I'd like to suggest you use the date function to define the filter ranges instead of the time intelligence function. It more suitable for accurate calculate with custom date ranges.
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
- AnonymousNot applicable
Hi!
Thank you for the answer.
So you are suggesting that I replace:
DATESINPERIOD( 'NOR Orders'[Date], 'NOR Orders'[Date]-YEAR(1), 1, YEAR ),With something like "Total Last Year to Date":
TITHW_TotalLYTDHW = VAR __MaxYear = MAX('Years'[Year]) VAR __MaxMonth = MAX('Months'[MonthSort]) VAR __TmpTable = CALCULATETABLE('TheHardWay',ALL('Years'[Year]),All('Months'[Month])) RETURN SUMX(FILTER(__TmpTable,[Year]=__MaxYear-1 && [MonthSort] <= __MaxMonth),[Value])After reading the post "TITHW" a couple of times I am still not sure how to apply it. I also have proper dates in a column and not only year and month as mentioned in the post, if that makes any differance.
- AnonymousNot applicable
HI Anonymous,
Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
- AnonymousNot applicable
Roger!
Try this: OneDrive Data
- AnonymousNot applicable
HI Anonymous,
It seems like you direct shared the calculated formula results in excel sheet instead of the raw value field.
For this scenario, you can take a look a t following sample formula and replace the fields with your data model table fields:TotalLYTDHW = //replace this to the calendar table date field that use as chart axis VAR currDate = MAX ( Date[Date] ) RETURN SUMX ( FILTER ( ALLSELECTED ( Fact ), Fact[Date] = YEAR ( curDate ) - 1 && Fact[Date] <= currDate ), [Value] )Regards,
Xiaoxin Sheng