Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Smart people needed (a date classification issue)

Hi All,

 

I have a kind of classification issue. Our department wants to distinguish between New and Existing (bestaande) business.

- New Business is classified as the first date on which there is turnover + 365 days.

- After a 365 days period, starting at the first date, the turnover is classified as 'Bestaande Business' or in English existing business.

 

This is simply done by the following formula ( IF(Easyflex_data[ndate] < (Easyflex_data[MinDate EF] + 365) ; "New Business"; "Bestaande Business").

But our department has another rule, which is: If the business has a discontinuity greater than 365 days (of no turnover), than the turnover must get the label new business again. See the following example:

Good classification of new business


The first row of turnover for this client is correctly classified as new business

Good classification of existing (bestaande business)

A example of a good transition from New tot Existing (bestaande business) on 04-01-2016.

Bad classification of existing business after a discontinuity periode 

from 28-08-2017 the turnover has to be classified as new business again because the discontinuity period is longer than 365 days. (04-04-2016 and 28-08-2017). Than the turnover has to be classified as new business till 28-08-2018 (which is one year or 365 days later). Thus 30-10-2017 and 06-11-2017 are wrongly classified as existing (bestaande) business.

Who can help me out with the right formula? 
Many thanks in advance. 

Ronald

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Vvelarde,

    Thank you very much, we are almost there. 

    This works wonderful for a database with a single client. But we encounter a database with multiple clients, thus we have to add another argument to the formula. Something like:

    Easyflex_data[RlIdNr] = EARLIER(Easyflex_data[RlIdNr]

    I'm trying to add this to your min +365 formula, but i can't figure out how to add a second filter argument:

    MinDate + 365 = 
VAR _ContextDate= Easyflex_data[ndate]
VAR FilteredTable= FILTER(Easyflex_data;Easyflex_data[ndate] <= _ContextDate && Easyflex_data[Discontinued]="Yes")
VAR _Exist_PreviousDiscountinued = COUNTROWS(FilteredTable)
VAR Result = If (_Exist_PreviousDiscountinued > 0; CALCULATE(MAX(Easyflex_data[ndate]);TOPN(1;FilteredTable;[ndate];DESC));Easyflex_data[MinDate EF])
RETURN
Result + 365

     Like this?

     FILTER(ALL(Easyflex); Easyflex_data;Easyflex_data[ndate] <= _ContextDate && Easyflex_data[Discontinued]="Yes"); Easyflex_data[RlIdNr] = EARLIER(Easyflex_data[RlIdNr]))

     Many thanks!

    Ronald

  • Vvelarde's avatar
    Vvelarde
    8 years ago

    Anonymous

     

    Try with :

    VAR FilteredTable= FILTER(Easyflex_data;Easyflex_data[ndate] <= _ContextDate && Easyflex_data[Discontinued]="Yes" && EasyFlex_Data[RIIdNr]=EARLIER (EasyFlex_Data[RIIdNr]) )

     Regards

     

    Victor

6 Replies

  • Gazzer's avatar
    Gazzer
    Icon for Resolver II rankResolver II
    8 years ago

    I feel like you need to do is compare MaxDate to today to see if it should be reverted to New Business.

     

    Something like this?

    IF( Easyflex_data[ndate] < (Easyflex_data[MinDate EF] + 365) OR Easyflex_data[MaxDate EF] < Today() ; "New Business" ; "Bestaande Business")

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Gazzer,

      Unfortunately, this solution does not work. Because the max-date relates to the last date we have from a customer in the database.

      Do you have any more ideas? Maybe something with a variable or something like that?

       

      Ronald

  • KarelV's avatar
    KarelV
    New Member
    8 years ago

    Hi All!

     

    I'm a colleague of Ronald, who initiated this thread. We are still facing the same problem. Today we wrote a new formula using the 'EARLIER' function in DAX for our calculated column. We seem to keep getting an error because the column name we are trying to refer to in the formula can't be found. 

     

     

     

     

    What we are trying to do with the PREVIOUS function is actually what the 'LAG' function in R would do. Do any of you know if we are using the 'PREVIOUS' function in a wrong way? 

     

    We would really appreciate your input!

     

    All the best, 

     

    Karel

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Vvelarde,

        Thank you very much, we are almost there. 

        This works wonderful for a database with a single client. But we encounter a database with multiple clients, thus we have to add another argument to the formula. Something like:

        Easyflex_data[RlIdNr] = EARLIER(Easyflex_data[RlIdNr]

        I'm trying to add this to your min +365 formula, but i can't figure out how to add a second filter argument:

        MinDate + 365 = 
VAR _ContextDate= Easyflex_data[ndate]
VAR FilteredTable= FILTER(Easyflex_data;Easyflex_data[ndate] <= _ContextDate && Easyflex_data[Discontinued]="Yes")
VAR _Exist_PreviousDiscountinued = COUNTROWS(FilteredTable)
VAR Result = If (_Exist_PreviousDiscountinued > 0; CALCULATE(MAX(Easyflex_data[ndate]);TOPN(1;FilteredTable;[ndate];DESC));Easyflex_data[MinDate EF])
RETURN
Result + 365

         Like this?

         FILTER(ALL(Easyflex); Easyflex_data;Easyflex_data[ndate] <= _ContextDate && Easyflex_data[Discontinued]="Yes"); Easyflex_data[RlIdNr] = EARLIER(Easyflex_data[RlIdNr]))

         Many thanks!

        Ronald