Smart people needed (a date classification issue)

[Anonymous]

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

[Anonymous · Accepted solution]

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

[KarelV]

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

[Vvelarde]

@KarelV @Anonymous

Hi, please review the PBIX.

Let me know if works on your scenario

Regards

Victor

[Anonymous · Accepted solution]

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 · Accepted solution]

@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

[Gazzer]

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]

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