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

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

Vvelarde's avatar
Vvelarde
Community Champion
8 years ago

KarelV Anonymous

 

Hi, please review the PBIX.

 

Let me know if works on your scenario

 

Regards

 

Victor

  • 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

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      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