Repurchase rate

[Anonymous]

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:

1. OrdNo as unique identifier for the orders
2. CustNo as identifier for the customer on the order
3. Date column
4. +Columns to filter on

I need:

1. Calculated column/Measure with the number of orders
2. Calculated column/Measure with the number of orders that has a reccuring order in the last 12 month
3. Calculated column/Measure with repurchase rate

I tried:

1. 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?

[Anonymous]

Roger!

Try this: OneDrive Data 

[v-shex-msft]

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

[v-shex-msft]

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

[Anonymous]

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.

[v-shex-msft]

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

[Anonymous]

Sure thing!

See attached file data.xlsx for base data.

And I would like to display it like this, see image below, where I get the repurchase rate in % on one axis and on the other axis  number of purchases for the chosen period and of those how many had a previous purchase.

[v-shex-msft]

Hi @Anonymous,

I can't found the sample file, can you please fix it? It is hard to test or troubleshoot from the snapshots.

Regards,
Xiaoxin Sheng