Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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:

  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?

7 Replies

  • Anonymous's avatar
    Anonymous
    Not 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

    • Anonymous's avatar
      Anonymous
      Not 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.

      • Anonymous's avatar
        Anonymous
        Not 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

    • Anonymous's avatar
      Anonymous
      Not 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