Forum Discussion

abloor's avatar
abloor
Helper IV
6 years ago
Solved

Time since last spend column

Hi,

I’m looking to create a report to show which of our clients are New, Lost or Retained.
New = spent in the last 13 months only
Lost= haven’t spent in the last 13 months
Retained = have spent in the last 13 months and greater than 13 months.

I’m thinking something like:
if Client last spend is less than 13 months and not greater than 13 months, then call it New Business.
If spend is greater than 13 month and not less than 13 months, then lost business.
If spend greater than 13 months and less than 13 months, then retained business.

Is this the best way to go around it, and how would you write the dax?

My data has multiple rows per client booking. Eg if a client made a booking to have various of our products delivered in Mar, Apr and May, then there would be a row for each month for each product type.
Not sure if I first need a separate column (or table?) that gives me a consolidated view of every month each client has spent?

Thanks very much
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Assuming the formula you had only need the date change

     

    ClientRetention =
VAR _today = max('Date'[Date])
VAR max1 = CALCULATE ( MAX ( 'Data'[Month]), ALLEXCEPT ( 'Data', 'Data'[ClientName] ) )
VAR min1 = CALCULATE ( MIN ('Data'[Month]), ALLEXCEPT ( 'Data', 'Data'[ClientName] ) )
RETURN
IF ( DATEDIFF ( max1, _today, MONTH ) > 13, "Lost",
IF ( DATEDIFF ( min1, _today, MONTH ) = 1, "New",
"Retained" )
)

     

6 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi abloor ,

     

    Here is my sample data.

    You need to create a new measure.

    Status =
VAR max1 =
    CALCULATE ( MAX ( test[Date] ), ALLEXCEPT ( test, test[CustomID] ) )
VAR min1 =
    CALCULATE ( MIN ( test[Date] ), ALLEXCEPT ( test, test[CustomID] ) )
RETURN
    IF (
        DATEDIFF ( max1, TODAY (), MONTH ) > 13,
        "Lost",
        IF ( DATEDIFF ( min1, TODAY (), MONTH ) <= 13, "New", "Retained" )
    )

    Here is the result.

     

    • abloor's avatar
      abloor
      Helper IV
      6 years ago

      Thanks v-eachen-msft I think that might be working to some degree, but I need some help tweaking it please.   Can you please advise me how to get it based on x months from slicer date, not x months from today?  We may need to get this information retrospectively, not always based on what it is on day of report usage.

       

      Also I made an error in my inital post.  A  'New' client is actually one that has spent in the chosen slicer month, but NOT in the 12 prior months.  e.g. they have spent in Aug 2019, but not Aug18-Jul19. How would I alter the line regarding a new client below to make this work?

       

      Here is my code

       

      ClientRetention =
      VAR max1 = CALCULATE ( MAX ( 'Data'[Month]), ALLEXCEPT ( 'Data', 'Data'[ClientName] ) )
      VAR min1 = CALCULATE ( MIN ('Data'[Month]), ALLEXCEPT ( 'Data', 'Data'[ClientName] ) )
      RETURN
      IF ( DATEDIFF ( max1, TODAY (), MONTH ) > 13, "Lost",
      IF ( DATEDIFF ( min1, TODAY (), MONTH ) = 1, "New",
      "Retained" )
      )
       
      Thank you very much :-)
      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Will taking a max of slicer date and use in place of Today(), solve the purpose?

         

         

        VAR _today = max('Date'[Date])

         

        Where data[date] is the date of the slicer.