Forum Discussion

abloor's avatar
abloor
Icon for Helper IV rankHelper IV
7 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 ...
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" )
)

     

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity 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
    Icon for Helper IV rankHelper 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
      Icon for Super User rankSuper 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.

       

      • abloor's avatar
        abloor
        Icon for Helper IV rankHelper IV
        6 years ago

        Thanks for your quick reply amitchandak 

        I'm only new to PBI and don't understand your solution.  Can you please explain it in simple terms for a newbie?

         

        e.g. if I need to change the dax in my above post, what bit do I change? Do I need to create any new measures or calc columns?

         

        Much appreciated!!