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" )
)

     

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!!

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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" )
)